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

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

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