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

Topic

New Reply

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

Reply | Quote

Replies

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:

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

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote