How select a Sheet if the Name is stored in a variable

T

TeddyBear

In my macro I add sheets programatically based on values held in a variable.

e.g. Sheets.Add.Name = vCurrVendNos

vCurrVendNos is set to values in one of my sheets. I then copy and paste
selected cells/ranges between my sheets. My variable is called vCurrVendNos
an it has a value of "101030".

I am trying to do what this statement achieves. I used this simple statement
to test whether it works and it does.

Sheets("101030").Select.

But the name of the sheets changes and I cannot hard code the value of all
the sheets because it changes based on data within one of the sheets..

My problem is that when I use the following

Sheets(vCurrVendNos).Select
OR Sheets("vCurrVendNos").Select
OR Sheets("vCurrVendNos").Activate

I get my error routine being activated. I know this must be relatively
simple but I can't seem to get the syntax or commands right.
 
H

Harlan Grove

TeddyBear said:
In my macro I add sheets programatically based on values held in a
variable.
Click to expand...

This STILL isn't the appropriate newsgroup in which to ask questions about
macro programming in SPECIFIC applications.
e.g. Sheets.Add.Name = vCurrVendNos

vCurrVendNos is set to values in one of my sheets. I then copy and paste
selected cells/ranges between my sheets. My variable is called vCurrVendNos
an it has a value of "101030".
Click to expand...
....

Are you CERTAIN its value is a string rather than a numeric type?
My problem is that when I use the following

Sheets(vCurrVendNos).Select
OR Sheets("vCurrVendNos").Select
OR Sheets("vCurrVendNos").Activate

I get my error routine being activated. I know this must be relatively
simple but I can't seem to get the syntax or commands right.
Click to expand...

The second and third statements above are clear errors. "vCurrVendNos" is a
string constant, and unless you have a worksheet actually named vCurrVendNos
this SHOULD throw a runtime error.

If the first statement above throws a runtime error, it's a near certainty
your vCurrVendNos variable has numeric type. Try

Sheets(CStr(vCurrVendNos)).Select
 
Top