Excel - VBA Merging cells on separate worksheet

Question

I have sheet 1 active, and I'm trying to perform a merge operation on some cells in Sheet 2, but I'm getting a 'Method 'Range of object '_Worksheet' failed'' error.

Dim rawData As Worksheet

For i = 2 To 12 Step 2
    rawData.Range(Cells(2, i), Cells(2, (i + 1))).Merge
Next i

However it does work if I insert a rawData.Activate line beforehand. Is there any way to accomplish this without activating the sheet first?

related: [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Jun 30 '17 at 22:51

score 2 · Accepted Answer · answered Jun 30 '17 at 22:10

Range/Cells without a qualifying worksheet always refer to the activesheet (unless in a Worksheet code module, where they refer to that worksheet).

Using/relying on the default behavior (ie. relying on a certain sheet being active when your code runs) should be avoided whenever possible.

Dim rawData As Worksheet

With RawData
    For i = 2 To 12 Step 2
        .Range(.Cells(2, i), .Cells(2, (i + 1))).Merge
    Next i
End With

Excel - VBA Merging cells on separate worksheet

1 Answers1