Excel VBA Macro to save series of worksheets to one pdf then repeat for different series of worksheets

Question

Trying for a macro that prints groups of four worksheets to one PDF until all groups of worksheets in the workbook have been printed.

I have a workbook that contains many sets of worksheets.

The worksheets are in sets of four reports: A1, A2, A3, A4, B1, B2, B3, B4....

I would like to create a macro that allows me to print worksheets A1-A4 to one PDF, then repeat for worksheets B1-B4, C1-C4....

I would like to use a range of cells to name the PDFs. Perhaps on sheet A1, cells A1:A3 (sheet B1, cells A1:A3, etc).

The end result would be a folder that contains one PDF for each group of worksheets 1-4.

I have seen macros that do this for one set of worksheets or for selected worksheets, but cannot figure out how to have the macro repeat the process for another set of worksheets after it has completed the first set.

Any tips, hints, tricks, help or points in the right direction would be beneficent, magnanimous. Trying to save gobs of time printing reports every time someone makes a little update to the data...

The following macro (not mine) does this all beautifully for selected worksheets, but not for series of 4 worksheets over and over again.

Sub PDFActiveSheetNoPrompt()

'www.contextures.com
'for Excel 2010 and later
Dim wsa As Worksheet
Dim wba As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wba = ActiveWorkbook
Set wsa = ActiveSheet

For Each wsa In ActiveWorkbook.Worksheets

'get active workbook folder, if saved
strPath = wba.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strName = wsa.Range("A1").Value _
          & " - " & wsa.Range("A2").Value _
          & " - " & wsa.Range("A3").Value

'create default name for saving file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsa.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    'MsgBox "PDF file has been created: " _
     ' & vbCrLf _
      '& strPathFile

    Next

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler



End Sub

Chameleon Chameleon · Accepted Answer · 2018-05-10T12:37:51

You could amalgamate each set of 4 worksheets into a single worksheet for PDF creation (eg a new worksheet named "A_PDF" would contain data from A1 to A4). Use formulas or a macro to copy the data across, depending on the size and structure of each worksheet. Then modify your PDF macro so it only creates one PDF at a time, and create a master macro to loop through the *_PDF worksheets:

Sub MasterPDFCreator()

    Call PDFActiveSheetNoPrompt(Sheets("A_PDF"))
    Call PDFActiveSheetNoPrompt(Sheets("B_PDF"))
    Call PDFActiveSheetNoPrompt(Sheets("C_PDF"))

End Sub

Sub PDFActiveSheetNoPrompt(wsName as Worksheet)

'www.contextures.com
'for Excel 2010 and later
Dim wsa As Worksheet
Dim wba As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wba = ActiveWorkbook
Set wsa = wsName

'get active workbook folder, if saved
 strPath = wba.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strName = wsa.Range("A1").Value _
      & " - " & wsa.Range("A2").Value _
      & " - " & wsa.Range("A3").Value

'create default name for saving file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsa.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strPathFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
'confirmation message with file info
'MsgBox "PDF file has been created: " _
 ' & vbCrLf _
  '& strPathFile

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler

End Sub

Not the neatest solution perhaps but I haven't yet found another way of merging sheets into a single PDF.

Excel VBA Macro to save series of worksheets to one pdf then repeat for different series of worksheets

1 Answers