Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I email an inactive copy of a worksheet | Excel Discussion (Misc queries) | |||
shortcut key for selecting the inactive cells surrounding a range | Excel Worksheet Functions | |||
macro to change data in inactive sheet | Excel Discussion (Misc queries) | |||
macro can't form a Range on an inactive Worksheet | Excel Programming | |||
How to get the activecell of inactive worksheet ? | Excel Programming |