macro can't form a Range on an inactive Worksheet

[Peter Chatterton]

Hope this isn't too terse, but "my macro can't form a Range on an
inactive Worksheet" seems to cover it. It's Excel 2002 on XP. Hope
you can help, I'm baffled. Peter.

 

[Sharad Naik]

If you define the worksheet properly .. e.g.

ThisworkBook.Sheets("SheetName").Range("what_ever_range)

It will work irrespective of where you are writing the code, Worksheet code,
ThisWorkBook code or, Module code.

Sharad

 

[JE McGimpsey]

It's much too terse. You should have included the code you're trying to
use.

One way to "form a Range":

Dim rMyRange As Range
Set rMyRange = Sheets("Sheet2").Range("A1:J10")


but I have no idea whether that's what you mean...

 

[Rob van Gelder]

I don't understand what you mean by "form a Range". I'm guessing you mean
reference a range in a variable?

This example will error because Sheet2 is not active.
Uncomment the commented line to fix - perhaps this is your issue?

Sub test()
Dim rng As Range

Worksheets("Sheet1").Activate
Set rng = Worksheets("Sheet2").Range("A1:E5")

' Worksheets("Sheet2").Activate
rng.Select
End Sub

 

[Peter Chatterton]

Thanks Mangesh and JE