Using "Select Case" to print different sheets

[Dao Ha Quang]

Sub printsheet()
      Select Case Worksheets("TT").Range("A1").Value
        Case 1
            Sheet1.PrintOut
        Case 2
            Sheet2.PrintOut
        Case 3
            Sheet3.PrintOut
        Case 4
            Sheet4.PrintOut
        Case 5
            Sheet5.PrintOut
        Case Is > 5
            Exit Sub
    End Select
End Sub

I use this code to print sheets based on the value of cell "A1"
But when I want to print many different sheets, I don't know how,
For example:
cell A1 value is "1,2,3" will print Sheet1, Sheet2 ,Sheet3
cell A1 value is "1,3,4,5" will print Sheet1, Sheet3, Sheet4 ,Sheet5
...
Help me

 

[Alex Blakenburg]

Maybe something like this:

Sub printsheet()

Dim wsTT As Worksheet
Dim arrNumber As Variant
Dim NoOfSht As Long
Dim i As Long

Set wsTT = Worksheets("TT")
arrNumber = Split(wsTT.Range("A1").Value, ",")

For i = 0 To UBound(arrNumber)
    NoOfSht = Val(arrNumber(i))
    Select Case NoOfSht
        Case 1
            Sheet1.PrintOut
        Case 2
            Sheet2.PrintOut
        Case 3
            Sheet3.PrintOut
        Case 4
            Sheet4.PrintOut
        Case 5
            Sheet5.PrintOut
        Case Is > 5
            Exit Sub
    End Select

Next i
End Sub

 

[Herakles]

This will work for single or multiple sheets.

Public Sub subPrintOutSheets()
Dim arrPrint() As String
Dim i As Integer

    arrPrint = Split(Worksheets("TT").Range("A1").Value, ",")
        
    For i = LBound(arrPrint) To UBound(arrPrint)
        Worksheets(Val(arrPrint(i))).PrintOut
    Next i

End Sub

 

[Dao Ha Quang]

Alex Blakenburg THANK, this is exactly what i want​

Herakles this is printing in order of sheets, also ok but not suitable for me, thank​

 

[Alex Blakenburg]

Thanks for providing us both with feedback. Glad we could help.

 

[Herakles]

Alex Blakenburg THANK, this is exactly what i want​

Herakles this is printing in order of sheets, also ok but not suitable for me, thank​

This procedure will allow you to view any sheet irrespective of the sheet's code name numeric suffix.

No error is caused if the sheet does not exist and the code just goes onto the next sheet.

Public Sub subPrintPreview()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim arrSheets() As String
Dim i As Integer

On Error GoTo Err_Handler

    Set Wb = ActiveWorkbook
    
    arrSheets = Split(Range("A1"), ",")
    
    For i = LBound(arrSheets) To UBound(arrSheets)
        For Each Ws In Wb.Sheets
            If Val(Replace(Ws.CodeName, "Sheet", "", 1)) = arrSheets(i) Then
                Ws.PrintPreview
                Exit For
            End If
        Next Ws
    Next i
    
Exit_Handler:

    Exit Sub

Err_Handler:
    
    Resume Exit_Handler
    
End Sub

 

[Dao Ha Quang]

This procedure will allow you to view any sheet irrespective of the sheet's code name numeric suffix.

No error is caused if the sheet does not exist and the code just goes onto the next sheet.

Public Sub subPrintPreview()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim arrSheets() As String
Dim i As Integer

On Error GoTo Err_Handler

    Set Wb = ActiveWorkbook
   
    arrSheets = Split(Range("A1"), ",")
   
    For i = LBound(arrSheets) To UBound(arrSheets)
        For Each Ws In Wb.Sheets
            If Val(Replace(Ws.CodeName, "Sheet", "", 1)) = arrSheets(i) Then
                Ws.PrintPreview
                Exit For
            End If
        Next Ws
    Next i
   
Exit_Handler:

    Exit Sub

Err_Handler:
   
    Resume Exit_Handler
   
End Sub

Thank you very much but sorry I have already selected the above post as the solution, anyway thank you very much for your enthusiasm