Collection of Sheets using the CodeName

[Dexx]

Let's say that I have 3 Sheets named Sheet1, Player1 and Player2 (renaming the tab at the bottom).

This code Works:

Dim Num
Dim Players(1) as Variant
Players(0) = "Player1"
Players(1) = "Player2"

For Num = 0 To 1

Sheets(Players(Num)).Select

-- actions --

Next Num



But if one day I change the tab "Player1" into "Michael Jordan" the code above will not work.

What I need is to use the CodeName of the Sheet, the value in (name) as specified in http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

I don't need all the sheets in a workbook but just some of them, something like


For Num = 0 To 1

PlayerX.Select

-- actions --

Next Num

where PlayerX is an sheet object (required by Select)

Thanx

[Carim]

Hi,

Why not using in your VBA code ... Sheet1, Sheet2, Sheet3 ... irrespective of the changes you might do in the respective tabs ...

[Dexx]

Yes I wanna use Sheet1, Sheet2, Sheet3 but i need to use them in a for...next cicle


Sheet1.Select
-- code --
Sheet2.Select
-- code --
Sheet3.Select
-- code --

But how to do that with a for...next?

[Dexx]

In pseudo c++ would be something like

Sheet sheets[2] = {Sheet1, Sheet2, Sheet3};

for (int i=0;i<=2;i++) {
sheets[i].Select;
--code--
}

[Carim]

Hi,

VBA code would be ...

Please Login or Register  to view this content.

[Dexx]

Yes but I this way I'll copy data in all sheets, I need to copy in just some sheets.

Here is a sample attached

[MSP77079]

Hi Dexx,

I normally do this a different way, but your pseudo code made me think of this:

Please Login or Register  to view this content.

The only problem with that code is that it will only work if the code is in the workbook that contains the sheets with CodeNames "Player1" and "Player2". If the code is in "book1" and is looking for sheets in "book2" that have certain code names, then I am back at the way I normally do it ...

Please Login or Register  to view this content.

[Dexx]

Hi MSP77079, (I copied/pasted you name 'cause is simplier ),

with your first code I resolved my problem.
The code run in just one workbook so there will no be problems.

Thanx again, I saved much time with this