Can you use a variable to reference a worksheet?

Posted by sid on September 10, 2001 8:53 AM

I would like to use a variable to reference a worksheet. For example I would like to save data stored in a worksheet named for that day ie sept9, but I need a way to use the data for that day by entering the number (9). Any suggestions would be a great help.
Thanks

Posted by Damon Ostrander on September 10, 2001 10:54 AM

Hi Sid,

Yes, it is easy to do this. Just declare a worksheet object variable:

Dim DayWS As Worksheet

then in your code:

Set DayWS = Worksheets("sept" & DayNo)

where DayNo is an integer variable that contains the day number (9 in your example).

You can then either activate this worksheet

DayWS.Activate

or simply qualify all range objects on this worksheet using this worksheet object variable. For example, to set cell B4 on this sheet to 25:

DayWS.[B4] = 25

Happy computing.

Damon

Posted by Sid Taylor on September 10, 2001 2:54 PM

Thanks Damon,
I am a real rookie when it comes to code. How and where do I declare a worksheet object variable, and if I want to pull a particular column into a spreadsheet, how do I reference it. If I needed a1:a4 on sept9 dayWS!a1:A4.
Thanks so much for your help.

Posted by Damon Ostrander on September 17, 2001 10:00 AM

Hi again Sid,

In the example I provided, the code

Dim DayWS As Worksheet

declares the variable DayWS to be a variable that can be assigned to a Worksheet object (actually, it's vice versa). This declaration generally is done inside the macro you are writing, and must be before (above where) the variable is used.

I'm not sure how to answer the rest of your question because I don't know how it relates to the first one. Do you, for example, want to enter the month number into a cell and have a1:a4 on the same sheet brought over from the corresponding month's worksheet? Or are you wanting to do this entirely within VBA, in which case the month number would be computed or obtained some other way?

Damon

: Hi Sid,