Publish Specific pages from multiple sheets as PDF

[mister_m]

I have an excel file with 70 sheets named 1,2,3...and so on.
I have a sheet called "Summary" Which contains a summary of all reports.
In this sheet, B3:B72 contain sheet names (1, 2,...3)
Column S3:S72 contain desired file names for respective PDF files.

I want to create separate PDF files of pages 1-3 for each sheet.
Is it possible? How? If a code is required, what would it be?

 

[Domenic]

The following code assumes that the workbook running the code contains your Summary sheet, along with your other sheets. However, if you want the code to run based on the active workbook, replace...

Set sourceWorkbook = ThisWorkbook

with

Set sourceWorkbook = ActiveWorkbook

Also, I've assume that the file names listed in Column S do not contain the path. So change the path where specified in the code, accordingly. Here's the code...

Option Explicit

Sub PrintSheetsToPDF()

    Dim sourceWorkbook As Workbook
    Set sourceWorkbook = ThisWorkbook

    Dim summaryWorksheet As Worksheet
    Set summaryWorksheet = sourceWorkbook.Worksheets("Summary")
    
    Dim lastRow As Long
    With summaryWorksheet
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    
    If lastRow >= 3 Then
        'do nothing
    Else
        MsgBox "No data found!", vbExclamation
        Exit Sub
    End If
    
    Dim saveToFolderName As String
    saveToFolderName = "c:\users\domenic\documents\" 'change the path to folder accordingly
    If Right(saveToFolderName, 1) <> "\" Then
        saveToFolderName = saveToFolderName & "\"
    End If
    
    Dim sheetNameRange As Range
    Dim fileNameRange As Range
    With summaryWorksheet
        Set sheetNameRange = .Range("B3:B" & lastRow)
        Set fileNameRange = .Range("S3:S" & lastRow)
    End With
    
    Dim rowIndex As Long
    With sheetNameRange
        For rowIndex = 1 To .Rows.Count
            sourceWorkbook.Sheets(CStr(.Cells(rowIndex, 1).Value)).ExportAsFixedFormat _
                Type:=xlTypePDF, Filename:=saveToFolderName & fileNameRange.Cells(rowIndex, 1).Value, IgnorePrintAreas:=False, from:=1, To:=3, openafterpublish:=False
        Next rowIndex
    End With
    
End Sub

Hope this helps!

 

[mister_m]

The following code assumes that the workbook running the code contains your Summary sheet, along with your other sheets. However, if you want the code to run based on the active workbook, replace...

Set sourceWorkbook = ThisWorkbook

with

Set sourceWorkbook = ActiveWorkbook

Also, I've assume that the file names listed in Column S do not contain the path. So change the path where specified in the code, accordingly. Here's the code...

Option Explicit

Sub PrintSheetsToPDF()

    Dim sourceWorkbook As Workbook
    Set sourceWorkbook = ThisWorkbook

    Dim summaryWorksheet As Worksheet
    Set summaryWorksheet = sourceWorkbook.Worksheets("Summary")
   
    Dim lastRow As Long
    With summaryWorksheet
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
   
    If lastRow >= 3 Then
        'do nothing
    Else
        MsgBox "No data found!", vbExclamation
        Exit Sub
    End If
   
    Dim saveToFolderName As String
    saveToFolderName = "c:\users\domenic\documents\" 'change the path to folder accordingly
    If Right(saveToFolderName, 1) <> "\" Then
        saveToFolderName = saveToFolderName & "\"
    End If
   
    Dim sheetNameRange As Range
    Dim fileNameRange As Range
    With summaryWorksheet
        Set sheetNameRange = .Range("B3:B" & lastRow)
        Set fileNameRange = .Range("S3:S" & lastRow)
    End With
   
    Dim rowIndex As Long
    With sheetNameRange
        For rowIndex = 1 To .Rows.Count
            sourceWorkbook.Sheets(CStr(.Cells(rowIndex, 1).Value)).ExportAsFixedFormat _
                Type:=xlTypePDF, Filename:=saveToFolderName & fileNameRange.Cells(rowIndex, 1).Value, IgnorePrintAreas:=False, from:=1, To:=3, openafterpublish:=False
        Next rowIndex
    End With
   
End Sub

Hope this helps!

This is incredible!? Worked perfectly!

Can this be taken further to publish specific sheets out of the entire list? Like I want to publish sheets 7,33 and 41 only, for example.

 

[Domenic]

This is incredible!? Worked perfectly!

That's great, I'm glad I could help. And thanks for your feedback!

Can this be taken further to publish specific sheets out of the entire list? Like I want to publish sheets 7,33 and 41 only, for example.

It's not clear how you want to implement it. I guess one way would be to amend the list of sheet names in Column B, along with their corresponding file names in Column S. Otherwise, can you please elaborate?