Macro to PDF worksheets based on list of tab names

[anetomarques]

Hi Excel Wizards, greetings from Portugal

I have already checked every post about worksheet-to-pdf macros, and none does the trick for the problem I have... Can somebody please help me?

I have an excel file with about 50 tabs, and I wanted to put a button on my summary sheet that would only save as the same pdf file the tabs that are listed on my summary list (that changes everyday, based on a procv that i have going). The tab name is exactly the same as the one that appears on the list.

This would really save me a lot of time!!!

 

[Fishboy]

Hi Excel Wizards, greetings from Portugal

I have already checked every post about worksheet-to-pdf macros, and none does the trick for the problem I have... Can somebody please help me?

I have an excel file with about 50 tabs, and I wanted to put a button on my summary sheet that would only save as the same pdf file the tabs that are listed on my summary list (that changes everyday, based on a procv that i have going). The tab name is exactly the same as the one that appears on the list.

This would really save me a lot of time!!!

Hi anetomarques, welcome to the boards.

Assuming that you have a sheet called List Sheet with a list of the desired sheets to export in column A on that sheet, try the following code. You will need to amend the bold red part to suit where you want these exports saved to:

Sub ExportSheetsAsPDF()
' Defines variables
Dim Cell As Range, cRange As Range
Dim ws As Worksheet, wb As Workbook
Dim wsName As String, fPath As String
Dim LastRow As Long


' Disables screen updating to reduce flickder
Application.ScreenUpdating = False
' Disables alerts to prevent unwanted popups
Application.DisplayAlerts = False


' Defines LastRow as the last row of data on the List Sheet based on column A
LastRow = Sheets("List Sheet").Cells(Rows.Count, "A").End(xlUp).Row
' Set the check range as A1 to the last row of A of the List Sheet
Set cRange = Sheets("List Sheet").Range("A1:A" & LastRow)


' Define the desired file path for the new files (amend as required)
fPath = "C:\TestFolder"


' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is not blank then...
    If Cell.Value <> "" Then
        ' Update variable wsName as the cell value
        wsName = Cell.Value
        ' Export the used range of that sheet as a PDF to the specified folder called the same as the sheet name
        Sheets(wsName).UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            fPath & "\" & wsName & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If
' Move to next cell in check range
Next Cell


' Re-enable alerts
Application.DisplayAlerts = True
' Re-enable screen updating
Application.DisplayAlerts = True


End Sub

 

[anetomarques]

Hi anetomarques, welcome to the boards.

Assuming that you have a sheet called List Sheet with a list of the desired sheets to export in column A on that sheet, try the following code. You will need to amend the bold red part to suit where you want these exports saved to:

Sub ExportSheetsAsPDF()
' Defines variables
Dim Cell As Range, cRange As Range
Dim ws As Worksheet, wb As Workbook
Dim wsName As String, fPath As String
Dim LastRow As Long


' Disables screen updating to reduce flickder
Application.ScreenUpdating = False
' Disables alerts to prevent unwanted popups
Application.DisplayAlerts = False


' Defines LastRow as the last row of data on the List Sheet based on column A
LastRow = Sheets("List Sheet").Cells(Rows.Count, "A").End(xlUp).Row
' Set the check range as A1 to the last row of A of the List Sheet
Set cRange = Sheets("List Sheet").Range("A1:A" & LastRow)


' Define the desired file path for the new files (amend as required)
fPath = "C:\TestFolder"


' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is not blank then...
    If Cell.Value <> "" Then
        ' Update variable wsName as the cell value
        wsName = Cell.Value
        ' Export the used range of that sheet as a PDF to the specified folder called the same as the sheet name
        Sheets(wsName).UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            fPath & "\" & wsName & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If
' Move to next cell in check range
Next Cell


' Re-enable alerts
Application.DisplayAlerts = True
' Re-enable screen updating
Application.DisplayAlerts = True


End Sub

Hi Fishboy!

That's it! Thank you so much! Is there any way I can make it save as the same pdf file?

 

[Fishboy]

Hi Fishboy!

That's it! Thank you so much! Is there any way I can make it save as the same pdf file?

Hmm, how about this:

Sub ExportSheetsAsPDF()
' Defines variables
Dim Cell As Range, cRange As Range
Dim ws As Worksheet, wb As Workbook
Dim wsName As String, fPath As String, ArraySheets() As String
Dim LastRow As Long
Dim x As Variant


' Disables screen updating to reduce flickder
Application.ScreenUpdating = False
' Disables alerts to prevent unwanted popups
Application.DisplayAlerts = False


' Defines LastRow as the last row of data on the List Sheet based on column A
LastRow = Sheets("List Sheet").Cells(Rows.Count, "A").End(xlUp).Row
' Set the check range as A1 to the last row of A of the List Sheet
Set cRange = Sheets("List Sheet").Range("A1:A" & LastRow)


' Define the desired file path for the new files (amend as required)
fPath = "C:\TestFolder\"


' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is not blank then...
    If Cell.Value <> "" Then
        ' Re-dims the variable ArraySheets preserving any existing entries
        ReDim Preserve ArraySheets(x)
        ' Add the cell value to variable ArraySheets
        ArraySheets(x) = Cell.Value
        ' Increase variable x by 1 for value added
        x = x + 1
    End If
' Move to next cell in check range
Next Cell


' Select all sheets in ArraySheets
Sheets(ArraySheets).Select
' Export the selected sheets as a single PDF to the specified folder as "The active workbook name".pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fPath & ThisWorkbook.Name & ".pdf"


' Re-enable alerts
Application.DisplayAlerts = True
' Re-enable screen updating
Application.DisplayAlerts = True


End Sub