0

I have an Excel workbook with 2 worksheets, and the following VBA code:

Sub test()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
Set rng = wb.Sheets(1).Range(Cells(2, 2), Cells(4, 5)) 'This raises an error (runtime error 1004), when sheet 1 is not active
rng.Select

End Sub

When worksheet 1 is active, the code runs without error. When worksheet 2 is active, it raises a runtime error 1004 when assigning the range-.

How do I assign the range so that it always works whether the associated worksheet is active or not?

Martin F.
  • 57
  • 6
  • 2
    `With wb.Sheets(1)`, `Set rng = .Range(.Cells(2, 2), .Cells(4, 5))`, `End With`. Note the periods in front of both `Range` and `Cells`. – BigBen Nov 02 '22 at 18:14

0 Answers0