Selecting sheets based on values to print as one PDF

[firemanli]

Good day,
I have a workbook with multiple sheets in it.
On a Control Sheet, I have the following data:

Sheet Name	Print?
Cover Sheet	Print
Details	Print
Worksheet	No
ST-124	Print
Terms & Conditions	Print

I want a print button that will print one pdf file of all the sheets that are selected by the control sheet.

Thanks
Aaron

 

[MartinS]

Hi
Based on an answer to a similar question, try the code below. I assigned a range name 'ControlPanel' to the table in your example.
HTH
Martin

Dim strSheets As String
Dim i As Integer
Dim rngControl As Range
'Define the range object
Set rngControl = ThisWorkbook.Names("ControlPanel").RefersToRange
'Loop through the rows of the range
For i = 1 To rngControl.Rows.Count Step 1
    'Only where Print? column = 'Print'
    If StrComp(rngControl.Cells(i, 2).Value, "Print", vbTextCompare) = 0 Then
        'Append comma for all but first row
        If Len(strSheets) > 0 Then
            strSheets = strSheets & ","
        End If
        'Append sheet name
        strSheets = strSheets & rngControl.Cells(i, 1).Value
    End If
Next i
'Copy selected sheets to a new workbook
Sheets(Split(strSheets, ",")).Copy
'Export to PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\FileOutput.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
'Close the workbook
ActiveWorkbook.Close SaveChanges:=False