selecting sheets in VBA

[gfalc3194]

If I select the first sheet in a workbook, hold shift and go to the
last sheet, all are selected for printing. However, when I record this
function, the VBA code lists all sheets by name.

For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3'
the macro code recorded = Sheets(Array("sheet1", "sheet2",
"sheet3")).Select

I would like to know if there is a way to select from sheet1 to sheet3
without explicitly naming them. The problem is that in the actual
workbook, sometimes I add or delete sheets, and sometimes rename them
for clarity. I don't want to have to modify the macro everytime I do
that. (the first and last sheets can always remain the same).

 

[Andrew Poulsom]

The CodeNames are created by Excel. They are numbered Sheet1 ... Sheetn in the order you create them, and that order (the Index order) doesn't change even if you move the sheets about. You can see them in their original order in the Properties window in the VBE, along with their (normal) Names.

To select more than one sheet you must pass an array to the Sheets property. My code enables you to rename sheets without having to change the code. But if you have moved sheets about it may not work.

My code assumes that you want to start at the first sheet. It's not easily changed to cater for a different start sheet, because the loop assumes that x is both the array index and the worksheet index.

 

[gfalc3194]

One more question before I leave everyone alone. I not see the codenames in the VBA window. However, the first sheet in the workbook is sheet14. The forth sheet is actually sheet 181! I have sheets as high as 612. This workbook has evolved over 4 years, so I guess as sheets get deleted and added, the code names keep going up. This beig the case, what does the code look like to start at the first sheet (sheet14) and go through the 20th, which happens to be sheet23?

thanks again.

 

[Andrew Poulsom]

I don't think you can apply my code to your scenario. Better revert to an explicit array.

 

[ssingh75]

Hi Andrew,

Please let me know, how can in move to first sheet of the activeworkbook?

However i am trying with the Sheets(1).select
But it is moving to Sheet1 not first sheet of the workbook.

 

[Alphonse68]

Hi everyone. I know this is an ooooold post, but I have both a question about the code posted by Andrew, and a suggestion or comment.

When I try to run the code as is, I get a "Variable not defined" error for Sheet3 (the VBE is kind enough to highlight it specifically). How can I fix that? Do I need to have reference to one of the libraries or something out of the "ordinary" (as if VBA in itself weren't)?

As for the suggestion/comment, I have a very caveman-like routine which re-CodeNames worksheets according to their index numbers, which makes it easy for me when I need to do something like the OP was trying to do. Just an idea if someone wants to develop their own, which will probably be cleaner and more efficient than my "pile of stones" code, in which first re-codename them by adding the highest codename number plus 50 to their current codename number, then re-codenaming them again using their index number, so index and code number will be the same. This gets rid of codenames that no longer exist and reduce their number to a sequence that is easy to work with.

Thank you, and Cheers.

You can use the Codenames instead of the Names. They are unlikely to change.
Maybe this will help you:

Sub Test()
    Dim i As Integer
    Dim x As Integer
    i = Sheet3.Index
    Dim Arr() As String
    ReDim Preserve Arr(1 To i)
    For x = 1 To Sheet3.Index
        Arr(x) = Worksheets(x).Name
    Next x
    Sheets(Arr).Select
End Sub

 

[Johnny C]

Weird you posted on a thread 14 years old and I just looked at it.

You can avoid all this by using either of the following:-

For Each sht In ActiveWorkbook.Sheets
' Some examples of sheet properties 
   strSheetName = sht.Name
    strCode = sht.CodeName
    shtNum = sht.Index
    Debug.Print shtNum & " > " & strSheetName & " > " & strCode
Next sht

or

For intCounter = 1 to Activeworkbook.Sheets.Count
' Some examples of sheet properties 
   strSheetName = Sheets(intCounter).Name
    strCode = Sheets(intCounter).CodeName
    shtNum = Sheets(intCounter).Index
    Debug.Print shtNum & " > " & strSheetName & " > " & strCode
Next intCounter

Good idea about changing the codenames. If you wanted to distinguish between printable sheets and non-printable you could change the names to Print* or Sheet* with your new index number depending on whether it's a print sheet and use Instr("Print") on the sheet.name to see if the sheet should be printed in the VBA to print them all.

As regards your code being 'Caveman-like' - it works fine and efficiently and can't go wrong so it's not caveman-like at all lol - some of the quickest VBA code around for sorting arrays and lists works just like that.

 

[Alphonse68]

Thanks, Johnny. Your idea and code did help.
Hopefully someone else will also put this info to good use.
Cheers

 

[MARK858]

When I try to run the code as is, I get a "Variable not defined" error for Sheet3 (the VBE is kind enough to highlight it specifically).

Hi Alphonse68, don't shout but a silly question, you do actually have a sheet with the sheet codename (sheet codename not the name on the sheet tab) of Sheet3?
Just asking because that is exactly the message you will receive if you don't have a sheet codename of Sheet3 and it will highlight the For x = 1 To Sheet3.Index line.

 

[Alphonse68]

Hi Mark,
That's what I asked myself too. A valid question being that I failed to specify so when I posted. Funny how sometimes we forget that others cannot see what we can over here in our little worlds. Yes, I do. Since posting, I tested and it actually goes further: the wb I tried that code on has 22 pages, all tabs names have been changed, but the codenames remain from 1 to 22. I tried the code on a spanking brand new file with four sheets, but no joy. Then I changed to number to 1 and it worked on both files. However, changing the number to 2 and higher yielded the same 'Variable not defined' error on both files. Today, using that same sub along with the snippet that Johnny posted, I got the correct info (tab name & index) for Sheet1 on both files (I thought it was some bogus Excel thing that Sheet1 existed since it thinks Sheet2+ don't) ... and there is a sheet codenamed Sheet3 in each file too... stumped!!!

I hope this doesn't stump you as well.
Cheers

 

[MARK858]

I hope this doesn't stump you as well.

It does at the moment as the code works for me as long as I have a sheet with the codename Sheet3.
I will post back if I think of anything but in the meantime can you copy and paste the results you got in the Immediate window with the first code Johnny C posted i.e.

Sub testIt()
Dim sht as Worksheet
For Each sht In ActiveWorkbook.Sheets
' Some examples of sheet properties 
    strSheetName = sht.Name
    strCode = sht.CodeName
    shtNum = sht.Index
    Debug.Print shtNum & " > " & strSheetName & " > " & strCode
Next sht
End Sub

and also

Sub testIt()
Dim sht as Worksheet
For Each sht In ActiveWorkbook.WorkSheets
' Some examples of sheet properties 
    strSheetName = sht.Name
    strCode = sht.CodeName
    shtNum = sht.Index
    Debug.Print shtNum & " > " & strSheetName & " > " & strCode
Next sht
End Sub