Loop through selection of worksheets to print to pdf

lr1234

New Member
Joined
Feb 9, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that has 3 sheets per company and there are 10 companies. For example sheets 1,2,&3 are the 3 reports for company 1. Sheets 4,5,&6 are reports for company 2, and so forth. I want to create a macro that will group the reports according to company and print to pdf. I've searched and searched but haven't found the solution to my problem.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Welcome to MrExcel Message board.
What is structure of your sheets?
How many column you should Print for each page?
you can merge each 3 sheet to one & then print to pdf (if you have different column names add them after last column of previous Otherwise if you have same structure at each sheet, you can add it after last row of Previous sheet)
Which one is better for you?
 
Upvote 0
Try this macro. The PDFs are created in the same folder as the active workbook and with the file name as the name of the first sheet in each group.
VBA Code:
Public Sub Create_PDFs()

    Dim currentSheet As Worksheet
    Dim i As Long
    
    With ActiveWorkbook
        Set currentSheet = .ActiveSheet
        For i = 1 To .Worksheets.Count Step 3
            .Worksheets(Array(i, i + 1, i + 2)).Select
            .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.Path & "\" & .Worksheets(i).Name & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
        currentSheet.Select
    End With
    
End Sub
 
Upvote 0
Try this macro. The PDFs are created in the same folder as the active workbook and with the file name as the name of the first sheet in each group.
VBA Code:
Public Sub Create_PDFs()

    Dim currentSheet As Worksheet
    Dim i As Long
   
    With ActiveWorkbook
        Set currentSheet = .ActiveSheet
        For i = 1 To .Worksheets.Count Step 3
            .Worksheets(Array(i, i + 1, i + 2)).Select
            .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.Path & "\" & .Worksheets(i).Name & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
        currentSheet.Select
    End With
   
End Sub
So I tried this code and the lines in my table printed to pdf differently than the are in excel. Some of the lines are bold and I dont have any bold lines in my excel version.
 
Upvote 0
The macro is doing the same that a manual Save as PDF would do, so I'm not sure if you can avoid the bold lines. You could try replacing xlQualityStandard with xlQualityMinimum. Also try recording macros whilst experimenting with the different Save as PDF options to see if they give you the result you want.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
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