Multiple sheets to save as PDFs with multiple file names

[RichTom44]

Hello Everyone,

How can I able to save it as a PDF in multiple sheets with multiple file names? In the below sample, I want to save it as a PDF from FORM01 to FORM05 with every form as its file name. Is there a formula for VBA just in one click and I can save all the files? Thanks

 

[rpaulson]

if i understand correctly. you would like to save 5 PDF files for every record in you data. in your example. you have 5 records, therefore 25 forms would be created?. If that's what you want it will be pretty simple to come up with some code.

-Ross

 

[RichTom44]

Yes Sir. In my sample, I made a maximum of 20 items in my Data and I had to save them in 20 PDF forms. And each form has a different file name.

 

[jolivanes]

PDF Files of each sheet will be saved in the same folder where your excel workbook is saved.

Sub Save_As_PDF()
Dim i As Long
    For i = 1 To ThisWorkbook.Sheets.Count
        With Sheets(i)
            .PageSetup.PrintArea = .UsedRange.Address
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Sheets(i).Name & ".PDF"
        End With
    Next i
End Sub

@Ross
I understand it to be individual sheets. We'll see

 

[RichTom44]

PDF Files of each sheet will be saved in the same folder where your excel workbook is saved.

Sub Save_As_PDF()
Dim i As Long
    For i = 1 To ThisWorkbook.Sheets.Count
        With Sheets(i)
            .PageSetup.PrintArea = .UsedRange.Address
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Sheets(i).Name & ".PDF"
        End With
    Next i
End Sub

@Ross
I understand it to be individual sheets. We'll see

Hi Sir, thanks for your reply. This coding was to save the entire workbook. Also is there a way to save as a PDF from the selected sheets from FORM01 to FORM05? Also, each file has its file name. Thanks

 

[rpaulson]

the below code will create 100 pdf files.

Sub Create_PDF()
Application.ScreenUpdating = False

Dim rs As Worksheet, ws As Worksheet
Set rs = Worksheets("DATA")
lr = rs.Cells(Rows.Count, "A").End(xlUp).Row


MyPath = Application.ActiveWorkbook.Path & "\"

For r = 2 To lr

    For s = 1 To 5 'sheet names
        If s = 1 Then Set ws = Worksheets("FORM01")
        If s = 2 Then Set ws = Worksheets("FORM02")
        If s = 3 Then Set ws = Worksheets("FORM03")
        If s = 4 Then Set ws = Worksheets("FORM04")
        If s = 5 Then Set ws = Worksheets("FORM05")
       
        myFile = rs.Cells(r, "A") & " - " & ws.Name & ".pdf"
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyPath & myFile
       
    Next s
Next r
Application.ScreenUpdating = True
MsgBox  "Reports saved in: " & MyPath
End Sub

hth,
Ross

 

[jolivanes]

Re: "This coding was to save the entire workbook". Yes. I could not find a proper explanation of which files needed to be saved as PDF.
Re: "selected sheets" and "from FORM01 to FORM05" What is it? Sheets that are selected or Sheets which names start with "FORM" or only Sheets FORM01 to FORM05?
Re: "each file has its file name" Explain what this is meant to mean please.

 

[jolivanes]

This will save each sheet named "FORM" and something following this (or not) to the folder where your excel file is saved.
Is this what you want?

Sub Save_As_PDF_Version2()
Dim i As Long, sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
        If Left(sht.Name, 4) = "FORM" Then
            With sht
                .PageSetup.PrintArea = .UsedRange.Address    '<---- Probably needs changing
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & sht.Name & ".PDF"
            End With
        End If
    Next sht
End Sub

Please don't quote posts. Just creates not needed junk.
Referring to Post numbers and posters names is way more efficient.

 

[RichTom44]

the below code will create 100 pdf files.

Sub Create_PDF()
Application.ScreenUpdating = False

Dim rs As Worksheet, ws As Worksheet
Set rs = Worksheets("DATA")
lr = rs.Cells(Rows.Count, "A").End(xlUp).Row


MyPath = Application.ActiveWorkbook.Path & "\"

For r = 2 To lr

    For s = 1 To 5 'sheet names
        If s = 1 Then Set ws = Worksheets("FORM01")
        If s = 2 Then Set ws = Worksheets("FORM02")
        If s = 3 Then Set ws = Worksheets("FORM03")
        If s = 4 Then Set ws = Worksheets("FORM04")
        If s = 5 Then Set ws = Worksheets("FORM05")
      
        myFile = rs.Cells(r, "A") & " - " & ws.Name & ".pdf"
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyPath & myFile
      
    Next s
Next r
Application.ScreenUpdating = True
MsgBox  "Reports saved in: " & MyPath
End Sub

hth,
Ross

Hi Sir, thanks for your response. From this coding where I can rename my file name for each sheet like the below sample coding in yellow highlighted and for the save folder. Thanks