Save Multiple Sheets as Individual PDFs using single cell reference for file name

csimonds

Board Regular
Joined
Oct 2, 2011
Messages
73
Hi,
I would love some help with a spreadsheet I am trying to cleanup.
I would like to have a button in sheet1, that loops through all sheets in the workbook. (Sheets are added and deleted, so number of total sheets is dynamic)
As the macro loops through each sheet, if the letter "T" appears in cell AA1, then create a pdf copy of that sheet only, in current folder where workbook is saved, using cell A6 as the name.
If cell AA1 is empty do nothing and look for the next sheet.

Any help in achieving the above would be greatly appreciated.

Thank you
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try this then:

Code:
Sub PDF_Sheets()
    
Dim lngCounter As Long
Dim strFilePath As String
With ThisWorkbook
    strFilePath = .Path & "\"
    For lngCounter = 2 To .Sheets.Count
        With .Sheets(lngCounter)
            If .Range("AA1").Value = "T" Then
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilePath & .Range("A6") & ".pdf" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False
            End If
        End With
    Next lngCounter
End With
End Sub

I assumed you didn't want to pdf the first sheet.

Dom
 
Upvote 0
Try this then:

Code:
Sub PDF_Sheets()
    
Dim lngCounter As Long
Dim strFilePath As String
With ThisWorkbook
    strFilePath = .Path & "\"
    For lngCounter = 2 To .Sheets.Count
        With .Sheets(lngCounter)
            If .Range("AA1").Value = "T" Then
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilePath & .Range("A6") & ".pdf" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False
            End If
        End With
    Next lngCounter
End With
End Sub

I assumed you didn't want to pdf the first sheet.

Dom

Hi Dom,

Thank you very much for taking the time to provide the above code. Its perfect. Really appreciate your assistance!

Kind Regards
Carolyn
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top