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
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

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

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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
 

blkane

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

Forum statistics

Threads
1,081,532
Messages
5,359,359
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top