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

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.

Harlan Grove

"TeddyBear" <> wrote...
>In my macro I add sheets programatically based on values held in a
>variable.

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".
....

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.

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

Harlan Grove

"TeddyBear" <> wrote...
>In my macro I add sheets programatically based on values held in a
>variable.

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".
....

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.

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