Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Select Array of Sheets in Excel (Excel 2003)

    I am trying to select sheets in a workbook that meet a criteria. I'm storing names of the selected sheets in a dynamic array named InvestSheet. Since I don't know how many sheets I'll be selecting, what is the best way to select and print (or just print) these sheets?

    I saw that I could select using:

    Sheets(Array("Page 1", "Page 2", "page 3", "page 4", InvestSheet(1), InvestSheet(2))).Select

    but I don't know how to select or print all the sheets in the dynamic array. Would I select the array or sheets all at once based on the names stored in the array, or can I select a sheet and then loop thru and add more sheets to the selection?

    Thanks in advance.

    Stephen

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

    Re: Select Select Array of Sheets in Excel (Excel 2003)

    <code>
    Dim strSNames(0 To 2) As String
    Dim I As Long
    strSNames(0) = "Sheet1"
    strSNames(1) = "Sheet2"
    strSNames(2) = "Sheet3"
    For I = LBound(strSNames) To UBound(strSNames)
    If I = LBound(strSNames) Then
    Sheets(strSNames(I)).Select (True)
    Else
    Sheets(strSNames(I)).Select (False)
    End If
    Next I
    </code>
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Select Select Array of Sheets in Excel (Excel 2003)

    Or, more or less equivalent:

    Dim i As Integer
    Sheets(InvestSheet(LBound(InvestSheet))).Select
    For i = LBound(InvestSheet) + 1 To UBound(InvestSheet)
    Sheets(InvestSheet(i)).Select Replace:=False
    Next i

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Select Array of Sheets in Excel (Excel 2003)

    Thanks!

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts

    Re: Select Select Array of Sheets in Excel (Excel 2003)

    Or just:
    <code>Sheets(InvestSheet).Select</code>
    HTH
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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