macro can't form a Range on an inactive Worksheet
 

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".

macro can't form a Range on an inactive Worksheet
 

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=318972

macro can't form a Range on an inactive Worksheet
 

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

"Peter Chatterton" wrote in message
om...
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".

macro can't form a Range on an inactive Worksheet
 

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


In article ,
(Peter Chatterton) wrote:

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".