Define a range globally

  • Thread starter Thread starter Debbie
  • Start date Start date
D

Debbie

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
 
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.
 
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.)
 
Back
Top