Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: selecting sheets in VBA

  1. #1

    Default selecting sheets in VBA

    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",

    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).

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Bogota, Colombia

    Default Re: selecting sheets in VBA

    Just curious, why don't you send the entire workbook for print instead of selecting the sheets first ?


    but, if you want to select the sheets anyway, you can use their index number:


    to select the first two.

    Juan Pablo González

  3. #3
    Board Regular
    Join Date
    Dec 2002

    Default Re: selecting sheets in VBA

    Try ActiveWorkbook.Sheets.Select

  4. #4

    Default Re: selecting sheets in VBA

    I guess I forgot to mention that sheet1 through sheet3 are not the only sheets in the workbook. Its more like this:
    Sheet1, sheet2.....up to sheet6.
    I first want to print sheet1 through sheet3, then print sheet4 through sheet6. So I can't just print all the sheets in the workbook. Likewise, I can't just say, select sheets 1,2,3, because at sometime I may add sheet1b. Now I have four sheets to print and would have to change the macro.

  5. #5
    MrExcel MVP
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002

    Default Re: selecting sheets in VBA

    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
    End Sub

  6. #6
    MrExcel MVP CT Witter's Avatar
    Join Date
    Jul 2002
    Columbus, OH

    Default Re: selecting sheets in VBA

    Maybe try this: At the end is a modification to send all the sheets as a single print job for continous page numbering.


  7. #7

    Default Re: selecting sheets in VBA


    I duplicated your code exactly and just changed the name of sheet3 to the actual name. Two things: If the sheet name has spaces in it, I get a compile erroe, "expected end of statement". I changed the name to one with no spaces but then got a 424 error, "object required".

    Any ideas? Thanks

  8. #8
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002

    Default Re: selecting sheets in VBA

    Hi g,

    Your response to Andrew suggests that you have misunderstood his suggestion (either that or you've found a way to include illegal characters in the CodeName ).

    Try this - activate the VBE (Alt & F11) and look at the Project window (Ctrl & R if its not visible). For the workbook in question you will see the sheets listed. A new workbook would show as the first sheet : Sheet1(Sheet1). The first 'Sheet1' is the CodeName and the second one is the worksheet tab name.

    Now, you can easily change the tab name (and include spaces) but the CodeName can only be changed in the VBE (it can be changed via code but we won't get into that right now!). It is for this reason that it is often useful to use the CodeName of a sheet in your VBA routines. For example, instead of referring to the sheet in the above example as 'Worksheets("Sheet1")' you would simply use 'Sheet1'.

    So, in Andrew's example the 'Sheet3' was the CodeName and you should not amend it unless you have actually changed the CodeName.


  9. #9

    Default Re: selecting sheets in VBA

    Yes I was trying to change the codename! I didn't understand what it was.

    thanks to all.

  10. #10

    Default Re: selecting sheets in VBA

    Ok I'm still confused. I think using sheet names of sheet1 etc is what's doing it.

    The actual workbook contains 42 sheets. The 1st 20 is output, the next 22 is backup. Sometimes I want to print the 1st 20, numbered 1 - 20, and the backup, numbered 1 -22. Sometimes I just print the first 20 output sheets.

    So as not to confuse myself any more than I already am, lets say the sheets are called One, Two, Three, Four, .....Twenty. Backup One, Backup Two.....Backup Twenty-two.

    using codenames, would the the formula be i = Sheet20.index? In other words, are sheet1, sheet2, etc codenames for the placement of the sheet in the workbook? When I print backup, whould the codename be sheet 42, and the code would be "ReDim Preserve Arr(21 to i)"?

    I used a test workbook with 2 sheets in it, used i = sheet2, and it worked.
    When I tried the actual workbook, using i = sheet20, when I got to the Sheets(Arr).select statement, I got an error "Select method of sheets class failed". Also, for testing, I changed sheet20 to sheet3, hoping to just select 3 sheets. The first time, from stepping into the code, it seemed to select 20 sheets (how did it know to stop at 20???). A subsequent run gave me a subscript out of range error.

    I'm lost. Possibly hopeless.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts