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.

 

[Carolever]

I was trying to add this bit of code to try to set the theme colors, but I think I'm pasting it in wrong or something:

ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
"C:\Users\themeaddressinmycomputer\Evergreen.xml" _
)

It runs into an error either during or right before or after this step.

Any ideas?
Thanks!

 

[jeffmb]

I believe the problem is that the original workbook theme is not being copied to the workbook copy. To remedy this try copying the theme to your c drive. The following code should take care of that for you:

Option Explicit
Dim ws As Worksheet
Sub Print_PDF()
    Application.DisplayAlerts = True
    ' Copy original theme to local drive
    ActiveWorkbook.Theme.ThemeColorScheme.Save ("C:\Users\OfraAndJeff\Documents\myThemeColorScheme.xml")
    For Each ws In Sheets
            Sheets(ws.Name).Copy
            ' Load just saved theme from local drive file 
            ActiveWorkbook.Theme.ThemeColorScheme.Load ("C:\Users\OfraAndJeff\Documents\myThemeColorScheme.xml")
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\OfraAndJeff\Documents\" & ws.Name & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                False
            ActiveWindow.Close False
    Next
        Application.DisplayAlerts = True
        
End Sub

Let me know if this fixes your problem.

 

[Carolever]

Yes - This worked!

Thank you!

 

[cptr_guru]

Hi there.

I'm trying to do this same exact thing in Excel 2010. This is my very first attempt at using code. I tried:

If Not ws.Name = "Sheet1" And Not ws.Name = "Sheet2" And Not ws.Name = "Sheet3" And Not ws.Name = "Sheet4" And Not ws.Name = "Sheet5" And Not ws.Name = "Sheet6" 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

I am getting "Run-time error 1004: Copy method of Worksheet class failed." What am I doing wrong?

Thanks so much!

 

[Carolever]

Hi there.

I'm trying to do this same exact thing in Excel 2010. This is my very first attempt at using code. I tried:

If Not ws.Name = "Sheet1" And Not ws.Name = "Sheet2" And Not ws.Name = "Sheet3" And Not ws.Name = "Sheet4" And Not ws.Name = "Sheet5" And Not ws.Name = "Sheet6" 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

I am getting "Run-time error 1004: Copy method of Worksheet class failed." What am I doing wrong?

Thanks so much!

I'm not 100% sure, but maybe it has to do with the apostrophes in front of two of the lines; i think they mean that line is "commented-out". But this is just a guess.

 

[krecek]

This thread has been extremely helpful as I do not have any coding experience. On that note what would the addition to this code be so when the file saves it is "Original File Name_Tab Title.pdf"? Rather than now where it is just "Tab Title.pdf".

 

[jeffmb]

Add ThisWorkbook.Name & "_" to Filename:= in the code as follows:

Option Explicit
Dim ws As Worksheet
Sub Print_PDF()
    ' Copy original theme to local drive
    ActiveWorkbook.Theme.ThemeColorScheme.Save ("C:\Users\OfraAndJeff\Documents\myThemeColorScheme.xml")
    For Each ws In Sheets
            Sheets(ws.Name).Copy
            ' Load just saved theme from local drive file
            ActiveWorkbook.Theme.ThemeColorScheme.Load ("C:\Users\OfraAndJeff\Documents\myThemeColorScheme.xml")
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\OfraAndJeff\Documents\" & ThisWorkbook.Name & "_" & ws.Name & ".pdf", Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                False
            ActiveWindow.Close False
    Next
        
End Sub

 

[krecek]

Thanks for your help! Unfortunately this code only seems to work for workbooks that don't rely heavily on equations that pull information from other sheets or other programs. The specific problem I am having seems to be caused by a financial software known as F9. For some reason this same macro code for smaller simpler files will print aas it is supposed to but on these files connected with this excel like add on the code will only produce one pdf, as if I just printed to pdf through excel. Does anyone have an idea of what the solution would be?

 

[krecek]

So after monkeying around with the files a bit more it appears that the issue is simply that the file that I want to use the macro for is too large. Is there anyway around that? When I use the macro on the whole file I receive several different error codes but when I separate the sheets a bit the code works.

 

[bobbybrown]

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.

Just stumbled across this whilst looking for something else but this does what I'm looking for...is there a way to add a dialogue to this that will pop up when the macro runs and show the progress on the screen infront of all the flickering sheets?