0

I have the following Code that generates separate PDF Files for each rows of my excel sheet.

My goal is to generate a Single PDF file having a number of pages instead of seperate PDF Files.

Can any kind soul help me with this?

Option Explicit

Sub Create_PDF_Files()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim dsh As Worksheet
Dim tsh As Worksheet
Dim setting_Sh As Worksheet

Set dsh = ThisWorkbook.Sheets("Data")
Set tsh = ThisWorkbook.Sheets("Letter Template")
Set setting_Sh = ThisWorkbook.Sheets("Settings")

Application.DisplayStatusBar = True
Application.StatusBar = ""

Dim i As Integer
Dim File_Name As String

For i = 2 To dsh.Range("A" & Application.Rows.Count).End(xlUp).Row
            
        Application.StatusBar = i - 1 & "/" & dsh.Range("A" & Application.Rows.Count).End(xlUp).Row - 1
        
        tsh.Range("C2").Value = dsh.Range("A" & i).Value
        tsh.Range("C3").Value = dsh.Range("D" & i).Value
        tsh.Range("C5").Value = dsh.Range("E" & i).Value
        tsh.Range("C6").Value = dsh.Range("F" & i).Value
        tsh.Range("C7").Value = dsh.Range("G" & i).Value
        tsh.Range("C8").Value = dsh.Range("H" & i).Value
        tsh.Range("C9").Value = dsh.Range("I" & i).Value
        tsh.Range("C10").Value = dsh.Range("J" & i).Value
        tsh.Range("D12").Value = dsh.Range("K" & i).Value
        
        File_Name = dsh.Range("B" & i).Value & "_" & dsh.Range("C" & i).Value & "_" & dsh.Range("K" & i).Value & ".pdf"
        tsh.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Sheets("Settings").Range("F4").Value & "\" & File_Name

Next i
    
    Application.StatusBar = ""
    MsgBox "PDF files generated successfully."
    
End Sub
help-info.de
  • 6,695
  • 16
  • 39
  • 41
Tahjib Hassan Rhythm
  • 1
  • 2

1 Answers1

0

The line starting tsh.ExportAsFixedFormat... is inside the For... next loop. This is why it is generating a new PDF for each file.

There are three approaches you could try:

  1. Move that line below the next statement, and adjust your code so that your accumulating variable is fed into an overall variable, which you then export.

  2. Run it like you are now, but add a step at the end to combine all the generated files into one PDF.

  3. Find a different export function that generates a PDF page rather than file, and can aggregate at the end.

ed2
  • 1,457
  • 1
  • 9
  • 26
  • Dear Sir, Thank you for your response. After trying the 1st approach: only one PDF is generated fine but it contains only the last row of data of my excel file. – Tahjib Hassan Rhythm Aug 27 '20 at 07:26
  • You have done this bit "Move that line below the `next` statement,"... but you also need to do this bit "and adjust your code so that your accumulating variable is fed into an overall variable, which you then export." – ed2 Aug 27 '20 at 07:27
  • Can you please help me with what specific adjustments do I need to incorporate ? – Tahjib Hassan Rhythm Aug 27 '20 at 07:30
  • There are a few similar questions you could refer to and adapt, such as https://stackoverflow.com/questions/30164466/how-to-export-multiple-ranges-to-a-single-pdf, https://stackoverflow.com/questions/13804102/exporting-multiple-pages-to-pdf-in-a-specific-order – ed2 Aug 27 '20 at 07:46