How to select a sheet via a counter variable in vba?

Question

I want to select sheets in an excel document. They are all named, but I know that you can select a sheet using its intrinsic number (ie. even though the second sheet created is named "risk ranking", I can still select it via Sheet2.select). Is there anyway to set this up using a counter variable? Here is a simplified and isolated code line from the macro:

counter = 0

Sheet & counter & .Select

counter = counter + 1

Anyone have any ideas?

does "select" mean get the sheet variable (i.e. the Excel.Worksheet object) or doe sit mean set focus to that sheet so it's what the user sees? — Brad, Jul 06 '16 at 15:33
This is an X-Y problem. You ***think*** you need to `.Select` a sheet, [**but you don't**](http://stackoverflow.com/a/10717999/1188513). — Mathieu Guindon, Jul 06 '16 at 15:58
@Mat'sMug but if this is a UI requirement (i.e., activating the sheet so that it has focus for the user), then OP *does* need to select/activate the sheet, as mentioned in comment above :) — David Zemens, Jul 06 '16 at 16:08
@DavidZemens sure, fine. But if that's done in a loop, then no, it's not needed, unless screen flickering is a business/UI/UX requirement. — Mathieu Guindon, Jul 06 '16 at 16:10
@Mat'sMug I like my screen to flicker when I loop so I can get dizzy and leave work early claiming sickness =P — findwindow, Jul 06 '16 at 16:12
But OP has given no concrete information that suggests he's doing a loop. Sure, using a counter might imply there's a loop, but the requirement is that OP wants the user to *see the sheet*, which, if there is a loop, implies that maybe it requires user input eg thru a Input Box or other prompt. Your advice is correct in the general case, but might not be in specific case. — David Zemens, Jul 06 '16 at 16:19
OP, check out ["What is the XY Problem"](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem), as @Mat'sMug mentioned this is probably one of those. — BruceWayne, Jul 06 '16 at 16:29
@Mat's Mug Many users have pointed out that I should not be using select, and have linked me to that page. But the issue is that the macro generates new pages and cells, and these each need to be referred to and edited in a similar way each time a new set is generated, and the amount of them generated varies on the user's discretion each time it is run... the page you linked sets rng = a specific range... I can't set a specific range ever... so I have been using select and activecell to get around this. Is there a way to still avoid select for somting this complicated? — Runeaway3, Jul 06 '16 at 17:00
You're probably using an unqualified `Range` function, which implicitly refers to the `ActiveSheet`. Not sure what "I can't set a specific range ever" means, but you definitely don't need to `Select` or `Activate` anything to do what you're doing. — Mathieu Guindon, Jul 06 '16 at 17:02
I just meant that I can't ever set a variable like rng = Range("A1:B60") because the range needed to be modified is never the same on each iteration or loop of the macro. The macro is extremely long and complicated (some ~200 lines of code), and I have just set the Application.ScreenUpdating = false to get around the slowness issue of select, but I will take a look into it. — Runeaway3, Jul 06 '16 at 17:05

score 5 · Accepted Answer · edited May 23 '17 at 12:22

You can loop through worksheets in a workbook like this:

Sub PrintAllSheetNames()
    Dim index As Long
    For index = 1 To ActiveWorkbook.Worksheets.Count

        ActiveWorkbook.Worksheets(index).Select

        ' Prints the worksheet's name in the Immediate Window (CTRL+G in the VBEditor)
        Debug.Print ActiveWorkbook.Worksheets(index).Name

    Next
End Sub

But, what are you really trying to do? It's best to avoid .Select wherever you can.

If you just want the user to see the sheet, then use .Activate. But still, don't do that. Use this knowledge of how to loop through sheets, and then work directly with the data.

How to select a sheet via a counter variable in vba?

1 Answers1