Update PDF Print Macro

blkane

New Member
Joined
Nov 18, 2011
Messages
47
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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)?
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top