I have a series of worksheets in a workbook and a slew of macros that
work with them. One particular worksheet is used in all the macros.
I've looked around here and in Walkenbach, but can't find a way to
declare that worksheet name a Public variable or constant. It appears I
will have to start each macro with:

Set CtyExtr = ActiveWorkbook.Sheets("County Extract")

Is there a way to do this project wide?
Thanks for your help.

Try:

Dim CtyExtr As Worksheet

Sub FirstMacro()
Set CtyExtr = ActiveWorkbook.Sheets("Sheet1")

End Sub

Code should be in the ThisWorkbook module.

"davegb" wrote:

I have a series of worksheets in a workbook and a slew of macros that
work with them. One particular worksheet is used in all the macros.
I've looked around here and in Walkenbach, but can't find a way to
declare that worksheet name a Public variable or constant. It appears I
will have to start each macro with:

Set CtyExtr = ActiveWorkbook.Sheets("County Extract")

Is there a way to do this project wide?
Thanks for your help.

You can just refer to the sheet by its code name. The code name is defined in
the properties in the VBE at the very top of the list beside (Name). The
default is Sheet1, Sheet2, ... Change it to something more descriptive like
shtCtyExtr and you will be able to refer to it the same way you refered to
the object you were createing.

shtCtyExtr.Select

--
HTH...

Jim Thomlinson


"davegb" wrote:

I have a series of worksheets in a workbook and a slew of macros that
work with them. One particular worksheet is used in all the macros.
I've looked around here and in Walkenbach, but can't find a way to
declare that worksheet name a Public variable or constant. It appears I
will have to start each macro with:

Set CtyExtr = ActiveWorkbook.Sheets("County Extract")

Is there a way to do this project wide?
Thanks for your help.