Printing multiple worksheets as seperate PDF files

[JanetR]

How do I print several worksheets as seperate PDF files?

I have a master sheet for data entry (that doesn't need to be printed) and then multiple worksheets which need to be sent to different people but they cannot see the other worksheets. How do I print them to separate files so I don't have to print 50+ times?

I am using excel 2003.

Thanks.

 

[jeffmb]

The following code will create a separate pdf file for each worksheet in the workbook, except for the main sheet (in this example, sheet1). The file names will come from the sheet names. There is a 5 second wait to allow the pdf reader to open the pdf file so that the code can close it. The time might have to be adjusted.

Option Explicit
Dim ws As Worksheet
Sub Print_PDF()
    Application.DisplayAlerts = False
    For Each ws In Sheets
        If Not ws.Name = "Sheet1" Then
            Sheets(ws.Name).Copy
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\JohnDoe\Documents\" & ws.Name & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                True
            Application.Wait Now + TimeValue("00:00:05")
            SendKeys "%{F4}", True
            ActiveWindow.Close False
        End If
    Next
        Application.DisplayAlerts = True


End Sub

 

[jeffmb]

You can change OpenAfterPublish:= True to OpenAfterPublish:= False and eliminate the next couple of lines:

Option Explicit
Dim ws As Worksheet
Sub Print_PDF()
    Application.DisplayAlerts = True
    For Each ws In Sheets
        If Not ws.Name = "Sheet1" Then
            Sheets(ws.Name).Copy
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\OfraAndJeff\Documents\" & ws.Name & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                False
            ActiveWindow.Close False
        End If
    Next
        Application.DisplayAlerts = True
        
End Sub

 

[jpalex1986]

Hello guys.,

I've tried using the above code and my sheet showed me an Error 400, i modified the code a little,

This is how the code looks,

Sub Print_PDF()
Dim Awb As Workbook
Dim Snr As Integer
Set Awb = ActiveWorkbook
Snr = 1
Application.DisplayAlerts = True
For Each ws In Awb.Sheets
If Not ws.Name = "Sheet1" Then
'Sheets(ws.Name).Copy
Awb.Sheets(Snr).Copy
'Sheets(ws.Name).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Awb.Path & "\" & Awb.Sheets(Snr).Name & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Snr = Snr + 1
ActiveWindow.Close False
End If
Next ws
Application.DisplayAlerts = True

End Sub

But this only prints the same sheet again and again with different sheet names.

Any help would be hugely appreciated.

Thanks in advance.

 

[jeffmb]

I do not know why you are getting error code 400. The macro works fine for me. The code that you listed has two related problems. First, you are looping via sheet name but are copying via sheet number. The sheet names and numbers may not be in the same order. Second, you are not incrementing the sheet number after processing "Sheet1" thereby still referring to sheet number 1 when examining the second sheet to be read. I can see why you are getting 1 copy of sheet1, but not all being copies of that same sheet. Using your macro I get sheet1 plus all but 1 of the rest of the sheets.

 

[jpalex1986]

I do not know why you are getting error code 400. The macro works fine for me. The code that you listed has two related problems. First, you are looping via sheet name but are copying via sheet number. The sheet names and numbers may not be in the same order. Second, you are not incrementing the sheet number after processing "Sheet1" thereby still referring to sheet number 1 when examining the second sheet to be read. I can see why you are getting 1 copy of sheet1, but not all being copies of that same sheet. Using your macro I get sheet1 plus all but 1 of the rest of the sheets.

Thank you Jeff for the quick response,

I'm a newbie at macros and hence am not able to spot the errors, could you highlight the errors that i need to change for this macro to work..?

I've changed the code basically since i all i want to do is print all the worksheets on a particular workbook.

Thanks again,

Kind regards

 

[jeffmb]

Sorry it took me a while to get back to you. I modified your code and came up with the following:

Option Explicit
Sub Print_PDF()
Dim Awb As Workbook
Dim Snr As Integer
Dim ws As Worksheet
Set Awb = ActiveWorkbook


For Each ws In Awb.Sheets
    If Not ws.Name = "Sheet1" Then
        'Sheets(ws.Name).Copy
        Awb.Sheets(ws.Name).Copy
        'Sheets(ws.Name).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Awb.Path & "\" & Awb.Sheets(ws.Name).Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
        
        ActiveWindow.Close False
    End If
Next ws


End Sub

Please let me know how this works out for you.

 

[Carolever]

Both of these codes work to create PDFs for my document. However, when I use them the colors in my spreadsheet go back to office default instead of using my custom theme colors (that are needed when I upload them to the website). Can you add any code to get the correct colors to stay?

Thanks!

 

[jeffmb]

I tested the code against a workbook with multiple colors in the worksheets. I found the proper colors in the pdf files generated.Therefore, the only thing I can suggest is to throw the following line into your code and see if it works:

ActiveSheet.PageSetup.BlackAndWhite = False

Since the code is already working for me I cannot tell if this will help you. Please let me know your results.

 

[Carolever]

I pasted it in, the code ran, but there was no difference in the color. I'm not just using different colors, I'm using different 'theme' colors (like civic, clarity, angles, or apex) but it's a custom theme, not just different set colors.