VBA Macro that saves each tab in Workbook to a Separate PDF file

[DaleRW]

Hello,

I did some searching on here and didn't find exactly what I need. My knowledge of Macros is very limited. Luckily I have a co-worker who knows quite a bit more and understands the VBA Macros considerably more but neither of us are exactly sure how to do this.

I have a workbook that my co-worker helped design.

The numbers of these tabs are our location numbers.

Daily I have data I export from a website and put the raw data into this workbook template. Then we have two macros. The first on filters all the data into a summary page (sorted by branch and type of transaction) and then onto separate tabs for each of our company's locations. The second Macro saves each tab of the workbook into a separate pdf file. We have it working really well, however, there will always be at least one of these locations that have no data for a particular day (which location will vary by day) so I would like to figure out a way to tell the macro to only save the tabs with data into a pdf file.

The Macro we are using is:

Sub createPDFfiles()
Dim ws As Worksheet
Dim dtDate As Date
dtDate = Sheets("(01)").Range("K1").Text
Dim Fname As String
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Fname = ThisWorkbook.Path & "" & Format(dtDate, "mmdd") & "OM - CORP " & ws.Name


ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False
Next ws
End Sub

I appreciate any help you can give me. Having the macro save every tab whether there is data or not isn't the end of the world but it is cleaner if it only saves the tabs with data.

Thanks!

 

[dmt32]

Re: Help Editing VBA Macro that saves each tab in Workbook to a Separate PDF file

Hi untested but maybe something like this

Sub createPDFfiles()
    Dim ws As Worksheet
    Dim dtDate As Date
    dtDate = Sheets("(01)").Range("K1").Text
    Dim Fname As String
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        
        If Application.CountA(ws.Cells) > 0 Then
            Fname = ThisWorkbook.Path & "" & Format(dtDate, "mmdd") & "OM - CORP " & ws.Name
        
            ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=Fname, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False
        End If
        Next ws
End Sub

This assumes worksheet is completely blank - if however, worksheet has headers etc then set the count to allow for this.


Dave

 

[DaleRW]

Re: Help Editing VBA Macro that saves each tab in Workbook to a Separate PDF file

Thank you Dave for your response. This helps give my co-worker some ideas how to design things. The issue we have with what you have above is that every tab has hundreds of lines with formulas in them for the "filter" Macro. The one constant on each is that the same cell on each tab has a location total. How would we use the same If concept saying to look at this cell with the total and if it is $0 then don't save to pdf but if it is anything else save to pdf?

 

[dmt32]

Re: Help Editing VBA Macro that saves each tab in Workbook to a Separate PDF file

Try replacing this line:

If Application.CountA(ws.Cells) > 0 Then

with this

If ws.Range("A10").Value > 0 Then

change the range address shown in RED as required.

Dave

 

[DaleRW]

Re: Help Editing VBA Macro that saves each tab in Workbook to a Separate PDF file

That is perfect Dave!! Thank you so very much. It works perfectly! It is always the actual wording for the commands and the order of things that tends to mess me up.