Excel Print Worksheets by Name

Question

I've got an Excel 2007 Spreadsheet and I'd like to write a VBA procedure to print particular worksheets by name. How do I do this?

For example, I'd like to print "FirstSheet","ThirdSheet", and "FourthSheet" but not "SecondSheet".

Answer 1

Something similar to the following should work:

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
        If (sh.Name = "Sheet1") Then
           sh.PrintOut
        End If
Next sh

Answer 2

Public Sub PrintByName(Names As Variant)

  Dim s As Worksheet
  Dim i As Integer

  If IsArray(Names) Then
    For Each s In ActiveWorkbook.Worksheets
      For i = 0 To UBound(Names)
        If StrComp(s.Name, Names(i), vbTextCompare) = 0 Then
          s.PrintOut
        End If
      Next i
    Next s
  End If

End Sub

Call like:

PrintByName Array("FirstSheet", "ThirdSheet", "FourthSheet")

The nested loop is not optimal, in regards to runtime performance. With the limited number of sheets an Excel workbook can contain, I think this is negligible. However, using a Collection to contain the desired sheet names instead of an Array would be better.