macro can't form a Range on an inactive Worksheet

[Peter Chatterton]

You're right, here's the code:

Dim rNewRange As Range
Set rNewRange = ActiveWorkbook.Sheets(sInSheet). _
Range(Cells(1, 2), Cells(5, 6))
Set rNewRange = ActiveWorkbook.Sheets(sOutSheet). _
Range(Cells(1, 2), Cells(5, 6))

One or the other works depending on what worksheet
I'm starting the macro from.

It gives a 1004, "appl-def'd or obj-def'd error".

 

[mangesh_yadav]

instead of
Set rNewRange = ActiveWorkbook.Sheets(sInSheet). _
Range(Cells(1, 2), Cells(5, 6))

use
Set rNewRange = ActiveWorkbook.Sheets(sInSheet). _
Range(Sheets(sInSheet).Cells(1, 2), Sheets(sInSheet).Cells(5, 6))

- Mangesh

 

[Sharad Naik]

Are sInSheet and sOutSheet previously defined variables pointing to the
Sheet Index numbers or their Names?
Or are they actual names of the sheets?

If actual names, then these should be under quotation marks:

Set rNewRange = ActiveWorkbook.Sheets("sInSheet").
and
Set rNewRange = ActiveWorkbook.Sheets("sOutSheet").

Sharad

 

[JE McGimpsey]

This is a very common problem - Cells(), when not qualified, defaults to
the ActiveSheet, so


Set rNewRange = ActiveWorkbook.Sheets(sInSheet). _
Range(Cells(1, 2), Cells(5, 6))

is equivalent to

Set rNewRange = ActiveWorkbook.Sheets(sInSheet). _
Range(ActiveSheet.Cells(1, 2), _
ActiveSheet.Cells(5, 6))

Since ranges must be contained in only one sheet, you get an error if
sInSheet is not the ActiveSheet.

Instead, qualify the Cells call using either


Set rNewRange = ActiveWorkbook.Sheets(sInSheet). _
Range(ActiveWorkbook.Sheets(sInSheet).Cells(1, 2), _
ActiveWorkbook.Sheets(sInSheet).Cells(5, 6))


or the more efficient With...End With structure

With ActiveWorkbook.Sheets(sInSheet)
Set rNewRange = .Range(.Cells(1, 2), .Cells(5, 6))
End With