Printing multiple worksheets as seperate PDF files

JanetR

New Member
Joined
Jul 12, 2012
Messages
4
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jeffmb

Board Regular
Joined
Jul 18, 2008
Messages
184
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.

Code:
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

Board Regular
Joined
Jul 18, 2008
Messages
184
You can change OpenAfterPublish:= True to OpenAfterPublish:= False and eliminate the next couple of lines:

Code:
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

New Member
Joined
Dec 5, 2013
Messages
2
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

Board Regular
Joined
Jul 18, 2008
Messages
184

ADVERTISEMENT

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

New Member
Joined
Dec 5, 2013
Messages
2
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

Board Regular
Joined
Jul 18, 2008
Messages
184

ADVERTISEMENT

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

Code:
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

New Member
Joined
Feb 27, 2014
Messages
7
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

Board Regular
Joined
Jul 18, 2008
Messages
184
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:


Code:
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

New Member
Joined
Feb 27, 2014
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,140
Messages
5,599,964
Members
414,352
Latest member
macquarie_jchan58

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
Top