Variables in Statements

Posted by Cindy on February 06, 2002 2:50 PM

I want to write a procedure that will pick which sheet to copy based on a tab name that will change each month.

I have the tab name created in a cell of one worksheet, but how do I write a statement that substitutes the contents for that cell in the copy statement? Is this possible?

Posted by Ivan F Moala on February 06, 2002 11:21 PM

Hi Cindy
You should copy the sheet using the sheets code
name this does not change by changing the tab name.
To see this goto the VBA editor
Alt F11
Ctr + R (project explorer)

have a look under microsoft exel objects
you should see something like this

Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)

The name in brackets is the Sheet Tab names
The name to the left is the sheet code name.

You would typically copy like;

Sheets("Sheet name").copy

using the sheets code name you would do it like this

Sheet1.copy

Dosen't matter if the tab name has been changed
the sheet code name remains the same.

Try changing it and see what happens

HTH

Ivan