Macro not running in worksheet, Excel VBA

Question

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

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Oct 19 '13 at 15:16
In a Sheet module `Range(...)` refers to a range on the sheet the module represents, not the active sheet. Another good reason not to use Activate/Select. May be better to put the code in a standard module (and get rid of those Select's) — chris neilsen, Oct 19 '13 at 15:40

score 3 · Answer 1 · answered Oct 19 '13 at 16:02

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

+ 1 Nicely explained. – Siddharth Rout Oct 19 '13 at 18:32 — Siddharth Rout, Oct 19 '13 at 18:32

Macro not running in worksheet, Excel VBA

1 Answers1