VBA Save All Relevant Report into One Document

[tlc53]

Hi there,

I have a spreadsheet with input pages and then various tabs for the respective reports. Not all report pages are required, and some are hidden (not visible) based on the users' selections on the setup page.

I have a formulated a drop-down list, which shows the reports available for saving to the system. This works fine, however, I would like an option that saves all the relevant report pages into one document - "Save All Reports". Is there a way to select and save only the visible report pages or those in the formulated drop-down list? My drop-down list is located in U1:U8 and has the following options when all reports are available.

Please Select
Save All Reports
Header Report
Cashflow Report
Milksolids Report
Stock Report
Annual Report
Analysis Report


Thank you!

'Save Report Macros
    If Not Intersect(Target, Range("L4")) Is Nothing Then
        Select Case Range("L4")
            Case "Header Report": PDF_HEADER
            Case "Cashflow Report": PDF_CASHFLOW
            Case "Milksolids Report": PDF_MILKSOLIDS
            Case "Stock Report": PDF_STOCK
            Case "Annual Report": PDF_ANNUAL
            Case "Analysis Report": PDF_ANALYSIS
        
        End Select
    End If

Sub PDF_HEADER()
 Range("L4").Value = "Please Select"
 Dim wsA As Worksheet
 Dim wbA As Workbook
 Dim strTime As String
 Dim strName As String
 Dim strPath As String
 Dim strFile As String
 Dim strPathFile As String
 Dim myFile As Variant
 Dim blnWasSheetHidden As Boolean
 On Error GoTo errHandler

 Set wbA = ActiveWorkbook
 Set wsA = Sheet1
 
 '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 _
 & " " & Format(wsA.Range("A3"), "dd.mm.yy")

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

 'use can enter name and
 ' select folder for file
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strName & ".pdf", _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

 'export to PDF if a folder was selected
 If myFile <> "False" Then
 If wsA.Visible = xlSheetHidden Then
    wsA.Visible = xlSheetVisible
    blnWasSheetHidden = True
End If
 wsA.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=myFile, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=False
 If blnWasSheetHidden = True Then
    wsA.Visible = xlSheetHidden
 End If
 'confirmation message with file info
 MsgBox "PDF file has been created: " _
 & vbCrLf _
 & myFile
 End If

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

 

[Skyybot]

Kinda sounds like you just want to save a current version of the Workbook.?

 

[tlc53]

Kinda sounds like you just want to save a current version of the Workbook.?

No, that's not what I want to do. Of the 17 tabs the spreadsheet is made up of, I only want to save the relevant report tabs, which can between 3 and 6 tabs in total, depending on the users selection on the setup page. These reports are available for printing individually in the drop-down list, but I want a "Save All Reports" option, which saves all the relevant/available reports in one document. I am guessing this can be done by either referring to the formulated drop-down list (U1:U8), or stating that of all the report sheets 1-6, only select and save if the sheet visible.

 

[Skyybot]

As you suggested, you can loop through available Reports in the dropdown, creating a New Workbook with only relevant Sheets.

 

[tlc53]

I'm trying to combine some VBA codes together to get the result I am after. That is, to save multiple sheets as one pdf.
Can someone please tell me why this is not working? Thank you.

Sub PDF_ALL_REPORTS()
 'www.contextures.com
 'for Excel 2010 and later
 Range("K5").Value = "Please Select"
 Dim wsA As Worksheet
 Dim wbA As Workbook
 Dim iSheetList As Variant
 Dim strTime As String
 Dim strName As String
 Dim strPath As String
 Dim strFile As String
 Dim strPathFile As String
 Dim myFile As Variant
 Dim blnWasSheetHidden As Boolean
 On Error GoTo errHandler
 
 iSheetList = Array("Sheet1", "Sheet12", "Sheet3", "Sheet14", "Sheet15", "Sheet16")

 Set wbA = ActiveWorkbook
 Set wsA = Sheet2
 
 'get active workbook folder, if saved
 strPath = wbA.Path
 If strPath = "" Then
 strPath = Application.DefaultFilePath
 End If
 strPath = strPath & ""

 strName = "Cashflow & Budget Info - " & wsA.Range("D2").Value _
 & " PE " & Format(wsA.Range("D5"), "dd.mm.yy")

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

 'user can enter name and
 ' select folder for file
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strName & ".pdf", _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

 'export to PDF if a folder was selected
 If myFile <> "False" Then
 If iSheetList.Visible = xlSheetHidden Then
    iSheetList.Visible = xlSheetVisible
    blnWasSheetHidden = True
End If
 iSheetList.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=myFile, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=False
 If blnWasSheetHidden = True Then
    iSheetList.Visible = xlSheetHidden
 End If
 'confirmation message with file info
 MsgBox "PDF file has been created: " _
 & vbCrLf _
 & myFile
 End If

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

 

[tlc53]

I've still not been able to resolve this issue.
Is anybody able to help please?