Creating a pdf in Excel from multiple spreadsheets based on cell values

[rbeyer87]

I have an Excel workbook with multiple worksheets titled P1, P2, P3, etc. I would like to create a macro that combines multiple worksheets into one pdf, but only based on a particular cell value. For example, look at cell L1 in each spreadsheet and if the value equals 1 include it in the combined pdf, otherwise ignore that particular worksheet. Cell L1 would be a simple If...Then...Else statement based on other criteria in each worksheet.

I would also like to have the macro prompt the user for the file name and location prior to saving and opening the pdf. I have looked at a number of posts but can't find any that specifically address the cell value criteria. I am new to VBA so I apologize if this is an easy request. Thanks in advance.

 

[John_w]

See if you can adapt the code at https://www.mrexcel.com/forum/excel...-export-pdf-based-cell-value.html#post4705917. The question in that thread is very similar to yours.

For prompting the user for the file name and browsing to a folder location you can use either Application.GetSaveAsFilename or Application.FileDialog(msoFileDialogSaveAs).

 

[rbeyer87]

See if you can adapt the code at https://www.mrexcel.com/forum/excel...-export-pdf-based-cell-value.html#post4705917. The question in that thread is very similar to yours.

For prompting the user for the file name and browsing to a folder location you can use either Application.GetSaveAsFilename or Application.FileDialog(msoFileDialogSaveAs).

Thanks John, again a novice VBA guy here.... I am getting an error - Run-Time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving. This is my code as it stands right now:


ublic Sub Export_Sheets_To_PDF()
Dim saveInFolder As String
Dim replaceSelected As Boolean
Dim wsName As Variant

saveInFolder = "C:\Users"
If Right(saveInFolder, 1) <> "" Then saveInFolder = saveInFolder & ""

With ThisWorkbook
replaceSelected = True
For Each wsName In Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13", "P14", "P15", "P16", "P17", "P18", "P19", "P20")
If .Worksheets(wsName).Range("L1").Value = 1 Then
.Worksheets(wsName).Select replaceSelected
replaceSelected = False
End If
Next

.ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=saveInFolder & .Worksheets("Input").Range("B1").Text & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

.Worksheets("Input").Select True

End With

End Sub


Highlighted text above is where the debugger is highlighted. ALSO, what do I need to replace in this code to instead use the Application.GetSaveAs Filename command? Thank you!!

 

[John_w]

Try this:

Public Sub Export_Sheets_To_PDF()

    Dim PDFfileName As Variant
    Dim currentSheet As Worksheet, ws As Worksheet
    Dim replaceSelected As Boolean
    
    PDFfileName = "C:\Users\" & ThisWorkbook.Worksheets("Input").Range("B1").Text & ".pdf" 'optional - initial browse folder or folder and file name
    PDFfileName = Application.GetSaveAsFilename( _
        InitialFileName:=PDFfileName, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        FilterIndex:=1, _
        Title:="Save Sheets as PDF")

    If PDFfileName <> False Then
    
        With ThisWorkbook
            
            Set currentSheet = ActiveSheet
            
            replaceSelected = True
            For Each ws In .Worksheets
                If (ws.Name Like "P#" Or ws.Name Like "P##") And ws.Range("L1").Value = 1 Then
                    ws.Select replaceSelected
                    replaceSelected = False
                End If
            Next
                
            .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfileName, _
                Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
                
            currentSheet.Select True
    
        End With
        
    End If
    
End Sub

PS please use CODE tags when posting VBA code - click the # icon in the reply box.

 

[rbeyer87]

Thanks John, this did the trick. Appreciate the code and the education, I've learned a lot just working through this issue. I'll try to keep learning!!