VBA print sheets by cell value

user_5282448 0
Viewed 50

I saw some questions on here that touch the subject, but not specific enough so I could extrapolate what I need with the knowledge I have. I have a macro that prints 5 different sheets 4 times each that works fine, but I want to be able to choose which one I want printed with a table like the one below (a1:b5) so i can select multiple pages, but i have no idea how.
enter image description here

This is what I use now:

Sub PrintRow1()
'Prints CMR 1 to 5

Sheets("CMR 1").PrintOut , Copies:=4
Sheets("CMR 2").PrintOut , Copies:=4
Sheets("CMR 3").PrintOut , Copies:=4
Sheets("CMR 4").PrintOut , Copies:=4
Sheets("CMR 5").PrintOut , Copies:=4
End Sub
vba excel
2 Answers

The code below is easy to understand and to adapt to your needs

Sub PrintSelectedSheet()
    Dim ShNum As Integer, ShName As String
    
    ShNum = InputBox("Select the sheet you want to print:" & vbCrLf & _
                    "1 for CMR 1" & vbCrLf & _
                    "2 for CMR 2" & vbCrLf & _
                    "3 for CMR 3" & vbCrLf & _
                    "4 for CMR 4" & vbCrLf & _
                    "5 for CMR 5", _
                    "Sheet selection")
    Select Case ShNum
        Case Is = 1
            ShName = "CMR 1"
        Case Is = 2
            ShName = "CMR 2"
        Case Is = 3
            ShName = "CMR 3"
        Case Is = 4
            ShName = "CMR 4"
        Case Is = 5
            ShName = "CMR 5"
        Case Else
            ShName = ""
    End Select
    
    If ShName <> "" Then
        Sheets(ShName).PrintOut , Copies:=4
    Else
        MsgBox "Wrong selection", vbCritical
    End If
End Sub
user_42936130

Print Flagged Worksheets

Sub PrintFlaggedWorksheets()
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the source worksheet ('sws') (containing the 'table').
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    
    ' Write the values from the source range to a 2D one-based array,
    ' the source array ('sData').
    Dim sData() As Variant: sData = sws.Range("A1:B5").Value
    
    ' Declare additional variables used the first time in the loop.
    Dim dws As Worksheet ' Destination Worksheet
    Dim sr As Long ' Source Row
    Dim dwsCount As Long ' Number of Printed Worksheets
    
    ' Loop through the rows of the source array.
    For sr = 1 To UBound(sData, 1)
        ' Check if the value in the current row of the second column is an 'x'.
        If StrComp(CStr(sData(sr, 2)), "x", vbTextCompare) = 0 Then ' do print
            ' Attempt to reference the destination worksheet using the value
            ' in the current row of the first column as the worksheet's name.
            On Error Resume Next ' defer error trapping (ignore errors)
                Set dws = wb.Worksheets(CStr(sData(sr, 1)))
            On Error GoTo 0 ' turn off error trapping
            ' Check if a reference has been created.
            If Not dws Is Nothing Then ' the destination worksheet exists; print
                dws.PrintOut Copies:=4 ' print
                dwsCount = dwsCount + 1 ' count
                Set dws = Nothing ' reset the destination worksheet variable
            'Else ' the destination worksheet doesn't exist; do nothing
            End If
        'Else ' don't print
        End If
    Next sr
    
    ' Inform.
    MsgBox "Worksheets printed: " & dwsCount, vbInformation
    
End Sub
user_98140690
Related