Selecting Mulitple Sheets by their Code Index Names

Hi,

I know how to select multiple sheets within VBA via the

Please Login or Register  to view this content.

and i know how to select a single sheet via its code index name

Please Login or Register  to view this content.

but how do you select multiple sheets via their code index name??

I need this because the sheet name is not static and will continue to change depending on the sheet contents and so i cannot specify the sheets name in a VBA select, i need to use the Code Index Name

Many Thanks

Ah thats even easier as it selects from their order in the workbook, just as long as no one ever comes along and shifts the order around!!

I have 80 to select so dont fancy sticking in 80 ",", do you know how i can get around this? tried 1:80 and 1 to 80 but didnt work

How about:

Please Login or Register  to view this content.

Thanks, glad to have some options

I think the safest way to avoid any future codings errors from someone messing around with tabs is to select each sheet with its specific index name rather than its position in the workbook

Although this requires a long list of code

I will have to weigh up the pros and cons of each method but thanks for the help

Simon

It's a bit convoluted but you could probably use a loop like this if all your sheet codenames are Sheet1, Sheet2 etc.

Dim wS
For Each wS In ActiveWorkbook.Sheets
If Replace(wS.CodeName, "Sheet", "") <= 80 Then wS.Select False
Next

Ah ok i know what your trying to do their but i have other sheets in the workbook which i dont want to select so i would need to rename their codename and remember to do that for any new sheets that may be added in the future.

I think i will have to stick with the safest route as follows:

Please Login or Register  to view this content.

Hi,

Whilst working more on it i could actually do with some sort of Loop Function as i need to select sheet 1 then run a macro then select sheet 2 run the same macro and so on so my code curently is:

Please Login or Register  to view this content.

Seems a bit horific but it will work!! However if i could build some sort of loop function by using the CodeNames then great

Please Login or Register  to view this content.

So if i interpret that code correctly is does this:

For each worksheet in workbook

If CodeName includes "Sheet" then replace with blank to leave just the number

Then for all CodeNames less than or equal to 80

Worksheet select

Call Get_PL

Next Worksheet


This seems like it would work as there would never been any sheets added within the range of 1 to 80 so wont affect this code?

Yeah that is what it does, I tried it quickly and it seemed to work.

Unfortunately this gives me a Type Mismatch?

Originally Posted by yudlugar

Please Login or Register  to view this content.

I am getting a Next without For error with this one