Print Sheets Using Cell Values

[TellM1955]

I have a number of sheets which depending on the selection of ON in the grid shown would like to bring up the Print Dialog Box to print the sheet names in the hidden columns. Further if All is selected it prints all of the sheets listed. This grid is on a sheet called "Master" and the range used is J3:N10. Has anyone got a VBA solution to do this? Thank you.

 

[TellM1955]

Micron, firstly your knowledge far exceeds mine to which I am truely grateful for you assisting me.

I've made the changes as suggested, but then as you've said you, just get the next line as error. However, I commented the line and put in the code
Application.Dialogs(xlDialogPrint).Show
this allowed me to print to a PDF but it only printed the Master sheet 7 times.

 

[kevin9999]

Another option. Please try on a copy of your workbook.

Option Explicit
Sub Print_Selected()
    Dim ws As Worksheet, c As range, s As String
    Set ws = worksheets("Master")
    
    With ws
        If .range("K10") = "ON" Then
            For Each c In .range("M4:M9"): s = c
                worksheets(s).Select
                Application.Dialogs(xlDialogPrint).Show
            Next c
        Else
            For Each c In .range("K4:K9")
                If c = "ON" Then
                    s = c.Offset(, 2)
                    worksheets(s).Select
                    Application.Dialogs(xlDialogPrint).Show
                End If
            Next c
        End If
        If .range("L10") = "ON" Then
            For Each c In .range("N4:N9"): s = c
                worksheets(s).Select
                Application.Dialogs(xlDialogPrint).Show
            Next c
        Else
            For Each c In .range("L4:L9")
                If c = "ON" Then
                    s = c.Offset(, 2)
                    worksheets(s).Select
                    Application.Dialogs(xlDialogPrint).Show
                End If
            Next c
        End If
    End With
    Application.Goto ws.range("J3")
End Sub

 

[Micron]

This is what I ended up with

Sub PrintSheets()
Dim i As Integer
Dim sht As Worksheet
Dim pdfPath As String
Dim bolSkipK As Boolean, bolSkipL As Boolean

On Error GoTo errHandler
Application.ScreenUpdating = False
pdfPath = ThisWorkbook.Path & "\"
If UCase(range("K10")) = "ON" Then
    For i = 4 To 9
        Set sht = Sheets(CStr(range("M" & i)))
        sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath & range("M" & i), Quality:=xlQualityStandard
    Next
    bolSkipK = True
End If

If UCase(range("L10")) = "ON" Then
    For i = 4 To 9
        Set sht = Sheets(CStr(range("N" & i)))
        sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath & range("N" & i), Quality:=xlQualityStandard
    Next
    bolSkipL = True
End If

If Not bolSkipK Then
    For i = 4 To 9
        If UCase(range("K" & i)) = "ON" Then
            Set sht = Sheets(CStr(range("K" & i).Offset(0, 2)))
            sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath & range("M" & i), Quality:=xlQualityStandard
        End If
    Next
End If

If Not bolSkipL Then
    For i = 4 To 9
        If UCase(range("L" & i)) = "ON" Then
            Set sht = Sheets(CStr(range("L" & i).Offset(0, 2)))
            sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath & range("N" & i), Quality:=xlQualityStandard
        End If
    Next
End If

exitHere:
Set sht = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

pdf's created in the file folder as per the path string.

 

[TellM1955]

To you both many thanks. Both solutions work a treat. Kevin, I've gone with your solution as I'm able to choose where the outputs are printed too.
Hope you both have a great Christmas.
Cheers

 

[kevin9999]

To you both many thanks. Both solutions work a treat. Kevin, I've gone with your solution as I'm able to choose where the outputs are printed too.
Hope you both have a great Christmas.
Cheers

Glad we could help, and Merry Christmas to you too

 

[Micron]

Same here!