Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define a range globally
I have many macros that create separate pivot tables (on separate
sheets) based on the same range (my data source sheet). I tried defining the range globally but I get errors. I.E. In module1: Public rngRawData as range Then in the workbook_open event I want to set the range: Set rngRawData = ActiveWorkbook.Sheets("Data").Range("R1C1:R981C30" ) I get an object-defined or application-defined error. Can someone please tell me what I'm doing wrong? Thanks, Debbie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define a range globally
Debbie:
It worked for me if I used A1 notation: Range("$A$1:$AD$981") You can use Application.ConvertFormula("R1C1:R981C30", xlR1C1, xlA1) to convert from R1C1 if necessary. -- George Nicholson Remove 'Junk' from return address. "Debbie" wrote in message om... I have many macros that create separate pivot tables (on separate sheets) based on the same range (my data source sheet). I tried defining the range globally but I get errors. I.E. In module1: Public rngRawData as range Then in the workbook_open event I want to set the range: Set rngRawData = ActiveWorkbook.Sheets("Data").Range("R1C1:R981C30" ) I get an object-defined or application-defined error. Can someone please tell me what I'm doing wrong? Thanks, Debbie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define a range globally
Either:
Set rngRawData = ActiveWorkbook.Sheets("Data").Range("A1:AD981") or: With ActiveWorkbook.Sheets("data") Set rngRawData = .Range(.Cells(1, 1), .Cells(981, 30)) End With worked ok for me. (the top version worked ok for George, too.) But my real question is do you want activeworkbook or ThisWorkbook? If the worksheet data is in the same workbook as the code, then I'd use ThisWorkbook. (Just in case you ever decide to hide the workbook or make it an addin--then it won't be the activeworkbook.) Debbie wrote: I have many macros that create separate pivot tables (on separate sheets) based on the same range (my data source sheet). I tried defining the range globally but I get errors. I.E. In module1: Public rngRawData as range Then in the workbook_open event I want to set the range: Set rngRawData = ActiveWorkbook.Sheets("Data").Range("R1C1:R981C30" ) I get an object-defined or application-defined error. Can someone please tell me what I'm doing wrong? Thanks, Debbie -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define Name range | Excel Discussion (Misc queries) | |||
Define a range | Excel Discussion (Misc queries) | |||
how to define range names | New Users to Excel | |||
Define a range based on another named range | Excel Worksheet Functions | |||
Define a Range depending on where the EOF is | Excel Programming |