Trying to activate/Select Sheet using the name stored in a variable

[tbroniecki]

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.

 

[tbroniecki]

Thanks to Tom Ogilvy I was able to find a way of doing what I needed to

Tom said in a previous response to another question

"When you do

worksheets.Add

the sheet just created is the activesheet. If you will be changing another
sheet to the activesheet, just set a reference to this new sheet

Dim shNew as Worksheet
Dim shOld as Worksheet
set shOld = Activesheet
worksheets.Add
set shNew = Activesheet

Now shOld hold a referene to the original activesheet and shNew a reference
to the newly added sheet. (as an example)".

Because I add my sheets using a variable I now store the value of the new sheet immediately after my add and then activate the sheet when I need to.

Thx Tom for your answer to another question which helped me.....