Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Sheets by Name (VBA/MS Excel/97 on)

    As part of a project I have been working on, a user will need to print between 5 and 50 worksheets. To automate the printing I need to programmatically select the sheets before printing. (I imagine that the user will click a button, and the sheets will be selected by grouping and then printed without any other user intervention.)

    I do not know all the sheet names or the total count of them but they will always start with 'Work Sheet...' or 'Results Sheet...' (eg "Work Sheet", "Work Sheet (2)", "Work Sheet (3)", etc. or "Results Sheet", "Results Sheet (1)", "Results Sheet (2)", etc.).

    Is it possible to use a wildcard in VBA to select the sheets so that they can be printed? If so, any suggestions on some code to do that?

    Leigh

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Selecting Sheets by Name (VBA/MS Excel/97 on)

    If you just want to print all of the sheets whose name starts with "Work Sheet" or "Results Sheet", then you could do something like this:

    <pre>Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    If UCase(Left(oSheet.Name, 10)) = "WORK SHEET" Or UCase(Left(oSheet.Name, 13)) = "RESULTS SHEET" Then
    oSheet.PrintOut
    End If
    Next oSheet
    </pre>


    If you really need to select all of the sheets (to get page numbers?) then you could do something like this:

    <pre>Dim oWS As Worksheet, bWSFound As Boolean
    bWSFound = False
    For Each oWS In Worksheets
    If UCase(Left(oWS.Name, 10)) = "WORK SHEET" Or UCase(Left(oWS.Name, 13)) = "RESULTS SHEET" Then
    If bWSFound Then
    oWS.Select (False)
    Else
    oWS.Select (True)
    bWSFound = True
    End If
    End If
    Next oWS
    If bWSFound Then
    ActiveWindow.SelectedSheets.PrintOut
    End If
    </pre>

    Legare Coleman

  4. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Sheets by Name (VBA/MS Excel/97 on)

    Thanks Legare

    I have put your suggestions into practice and both work as intended. I imagined that there would be some kind of wildcard that VBA would use, but no, the LEFT function for the respective number of characters was all that was needed. Why didn't I think of that?

    Thanks again for your assistance - it was appreciated. (This reply is late because I thought I had already replied but it did not appear - I have to ensure that 'Post It' is the button I press!!)

  5. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Selecting Sheets by Name (VBA/MS Excel/97 on)

    Comment on "wildcard"; you might look at the VBA "LIKE" operator, though I'm not sure if it existed in XL 97. Sounds like LEFT does what you need in any case.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

Posting Permissions

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