Update PDF Print Macro

[blkane]

Hi, I have a very simple macro to print multiple worksheets to separate pdfs. It works fine but I need to clean up the results. I was hoping to get some help from the experts on this forum. I am printing customer invoices with each worksheet.

Here is the current code.

Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim wks As Worksheet
With ActiveWorkbook
sPath = .Path & "\"
For Each wks In .Worksheets
sFile = "09 2014 " & wks.Name & ".pdf"
wks.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=sPath & sFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next wks
End With
End Sub

I would like to improve this with the following features.

1. For the code noted in red (sFile): I would like to set the file name automatically to select the prior month in this format. {MM YYYY "worksheet name"}

2. For the code noted in blue (sPath): I would like to start in the current directory but create a folder called "Invoice Copies". Therefore the pathname would be the "current directory"\Invoice Copies\"new pdfs".

3. For the code noted in green (.worksheets): I would like to select only the invoice worksheets. There are about 4 worksheets that are data only and do not need to print.

Any help on any of the three items above is greatly appreciated.

Thank you

 

[BiocideJ]

1. For the code noted in red (sFile): I would like to set the file name automatically to select the prior month in this format. {MM YYYY "worksheet name"}

Where is the month information stored? Is it in a specific cell in the Worksheet you are printing?

3. For the code noted in green (.worksheets): I would like to select only the invoice worksheets. There are about 4 worksheets that are data only and do not need to print.

Is there some specific Naming convention to the sheets you want to print (or that you want to exclude)?

 

[blkane]

Thank you for the quick response. I responded below your questions.

Where is the month information stored? Is it in a specific cell in the Worksheet you are printing?

Is there some specific Naming convention to the sheets you want to print (or that you want to exclude)?

The month information is not currently located in any cell but I could create one if that is easier. I was thinking it would take the system date and calculated the prior month end.

I wanted the file names to be the prior month end + the tab name. i.e MM YYYY {tab name}.pdf

 

[BiocideJ]

I didn't see a response to Q#3, so I am assuming the data tabs have the text "data" in them and am excluding any tabs as such.

The following should give you more or less what you are looking for.

Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim wks As Worksheet

    With ActiveWorkbook
        sPath = .Path & "\Invoice Copies\"
        If Len(Dir(sPath)) = 0 Then MkDir sPath 'folder doesn't already exist
        
        For Each wks In .Worksheets
            If Not wks.Name Like "*data*" Then  'don't print data sheets
                sFile = Format(WorksheetFunction.EoMonth(Now, -1), "MM YYYY ") & wks.Name & ".pdf"
                wks.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=sPath & sFile, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=False, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False
            End If
        Next wks
    End With
End Sub

 

[blkane]

Thank you. This solves both #1 & #2. The word "data" is the first word in the tab name for all 4 sheets but they printed as well. But, I think I figured it out. "data" is case sensitive. Once, I fixed the case, it worked perfectly.

Thank you very much for your help!

 

[BiocideJ]

No problem. Glad to hear it is working for you.