CODEWITHSUNDEEP
vbaexcel
Shan S
Shan S

Reputation: 49

Macro not running in worksheet, Excel VBA

I just recorded this macro and paste it into my main code which is inside one of the sheets. When i run it with my code , it throws an error at this point , i dont know why ?.

Any idea ?

Sub pastem()
Windows("Main.xlsm").Activate
Sheets("Work").Select
Range("B6").Select        'error at this position
ActiveSheet.Paste 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Range("B6").Select
End Sub

Thanks

Upvotes: 0

Views: 4453

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

Range("B6") is an unqualified reference. Unqualified Range references in a standard module refer to the active sheet. Unqualified Range references in a sheet's class module refer to the sheet represented by the class module. Since your code is not in 'Works' class module, you're trying to select a range that's not on the activesheet, which you can't do. Here's an example that doesn't rely on a certain sheet being active. 

Sub pastem()

    With Workbooks("Main.xlsm").Sheets("Work").Range("B6")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False

End Sub

Upvotes: 3

Related Questions