Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to activate multiple worksheets in code (2000)

    I am looking for a way to activate multiple worksheets using VBA. I have used the Macro recorder to see how Excel does this......
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    'Sheets("Sheet3").Activate
    I have found you can also do this...
    Sheets(Array(1, 2, 3)).Select
    'Sheets(3).Activate

    The problem for me is that I need a dynamic version of this. One that does not rely on the same number of sheets or that they will be the same name. I could loop through the sheets in a workbook to determine the number to print but how do I pass that to this array?

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to activate multiple worksheets in code (2000)

    I'm throwing this at you hoping it makes sense. It doesn't use the array structure, it loops through all the Worksheets irrespective of name and number:

    Sub LoopThruAllSheets()
    Dim wsSheet As Worksheet
    Dim wsActvSht As Worksheet
    Set wsActvSht = ActiveSheet ' set starting sheet
    ' loop through all sheets in WB
    For Each wsSheet In ActiveWorkbook.Worksheets
    wsSheet.Activate ' may not be necessary to Activate depending on what Method you are doing
    ' see if worksheet is protected, may not be necssary for some Methods, such As PrintOut
    If Worksheets(ActiveSheet.Name).ProtectContents <> True Then
    ' <your code here>
    End If
    Next wsSheet
    wsActvSht.Activate ' go back to starting wsheet
    End Sub

    The three lines using wsActvSht are not necessary if you do not need to end up on the same Worksheet you started on.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to activate multiple worksheets in code (2000)

    Given that some criteria would be required in order to determine the sheets to select, the following example selects all sheets with an "x" in cell A1. <pre>Sub MultiSheetsSelect()
    Dim SheetArray() As Variant
    Dim ws As Worksheet
    Dim indx As Integer
    For Each ws In ThisWorkbook.Sheets
    If ws.Range("A1") = "x" Then
    ReDim Preserve SheetArray(indx)
    SheetArray(indx) = ws.Index
    indx = indx + 1
    End If
    Next
    If indx > 0 Then
    Sheets(SheetArray()).Select
    End If
    End Sub</pre>

    If you need a specific sheet to be 'active', you can include a line at the end.

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to activate multiple worksheets in code (2000)

    Andrew,
    Thanks for the code but I am unable to get it to work. I have modified the code to include a worksheet that does not have X in the range so I would not have to modify ny worksheet and I get the "Run-time error 1004" when I try ti use the code. Any thoughts?

    Ed

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to activate multiple worksheets in code (2000)

    Could you show us the code that does not work? Its kind of hard to debug code that you can't see. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to activate multiple worksheets in code (2000)

    Not a problem. Its the code in Andrew C's post ...

    Sub MultiSheetsSelect()
    Dim SheetArray() As Variant
    Dim ws As Worksheet
    Dim indx As Integer
    For Each ws In ThisWorkbook.Sheets
    If ws.Range("A1") = "x" Then
    ReDim Preserve SheetArray(indx)
    SheetArray(indx) = ws.Index
    indx = indx + 1
    End If
    Next
    If indx > 0 Then
    Sheets(SheetArray()).Select
    End If
    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to activate multiple worksheets in code (2000)

    Ed, Andrew's line of code

    ws.Range("A1") = "x" Then

    is an example of criteria you might use to add the specifc sheet to the array. Perhaps you could post your current version of the code and also tell us what is the basis for selecting the sheets to be operated on (certain contents or what)? Or do you want to operate on all worksheets in the workbook? Another approach might be to use Andrews code, but instead of that line, use

    ws.Name<> "NotThisSheet" Then

    so if the worksheet name is anything but "NotThisSheet", put it in the array
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to activate multiple worksheets in code (2000)

    I copied the code from your message and pasted it into my Xl2K and it works just as Andrew said it would. You said that you modified his code, that code is what we need to see.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to activate multiple worksheets in code (2000)

    The only change I made to Andrews original code was to replace = with <> so...
    ws.Range("A1") = "x" Then became..
    ws.Range("A1") <> "x" Then which ends up adding all the sheets in the work book which is what I want.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to activate multiple worksheets in code (2000)

    I just changed the "=" to "<>" and ran the code again. It still works perfectly. Could you maybe upload the workbook where it does not work? What line of the code is getting the error?
    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to activate multiple worksheets in code (2000)

    In that case, since you want all sheets, you don't need that line and the corresponding 'End If' at all; you just need:

    For Each ws In ThisWorkbook.Sheets
    ReDim Preserve SheetArray(indx)
    SheetArray(indx) = ws.Index
    indx = indx + 1
    Next

    but if it isn't working for you, there may be a protected sheet or something else. Could you post the workbook in question after deleting any proprietary content?
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to activate multiple worksheets in code (2000)

    Andrew's code works fine for me also.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to activate multiple worksheets in code (2000)

    Ed, are they all worksheets? If you want all the Worksheets in the book, these two lines do the same:

    ThisWorkbook.Activate
    ActiveWorkbook.Worksheets.Select

    You don't need Andrew's code unless you want to exclude certain sheets.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to activate multiple worksheets in code (2000)

    Thanks to everyone for their help. I found the problem. I apologize for waiting everyone's time on this one. It was my own fault. I had modified the original code from Andrew by including a statement to set the value of indx to 1 before the loop begin. The reason was the xla Add-in that I had placed the code in had the setting 'Option Base1' which starts arrays off at 1 instead of 0. I did this last week and now that I was back from vacation I wanted to do something similar as workbook macro so I copied and pasted the code from the xla Add-In. I couldn't figure out why it was working in the Add-in other then maybe the Add-ins original author had created the Add-in in a way that just worked. The Add-in's code is almost entirely Late-Binding and uses an older style of coding such as using symbols like $ (for string) so I figured that the thing was just ignoring the Subscript error somehow and moving forward. OH Well! Lesson learned.
    This is not the first time I have been thrown off because of working with older coding styles. Just out of curiosity does anyone have an opinion about things like using 'Option Base 1'? Every thing I have ever read about VB coding talks about staying away from the older style of writing code.

    Thanks Again!

Posting Permissions

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