[Solved] Print all sheets in a workbook, with same page layout format and etc

[bigme]

dear all,
kindly help, i need to print all sheets in a workbook, but when i do it manually, for every sheet i need to re arrange the margin, paper selection, and select printer, is there any possibility to do all this task with a macro so it will print all sheets automatically with same rules? thank you

regards,
bigMe

 

[Lutfor Rahman Shimanto]

dear all,
kindly help, i need to print all sheets in a workbook, but when i do it manually, for every sheet i need to re arrange the margin, paper selection, and select printer, is there any possibility to do all this task with a macro so it will print all sheets automatically with same rules? thank you

regards,
bigMe

Hello BigMe

Thanks for sharing your problem. You wanted to print all sheets in a workbook with the same page layout format, etc.

I am delighted to inform you that I have developed a sub-procedure to fulfil your goal.

I am assuming your data and sheets are like the following GIF.

​

To print all sheets in a workbook with the same page layout format:

1. Press Alt+F11 to open VBE.
2. Click on Insert followed by Module.
3. Insert the following code in the module and Run.
   

   Sub PrintAllSheetsWithSamePageLayout()
    
       Dim ws As Worksheet
    
       For Each ws In ThisWorkbook.Sheets
        
           ' Add more settings as needed
           With ws.PageSetup
               .LeftMargin = Application.InchesToPoints(0.75)
               .RightMargin = Application.InchesToPoints(0.75)
               .TopMargin = Application.InchesToPoints(1)
               .BottomMargin = Application.InchesToPoints(1)
               .Orientation = xlLandscape
               .PaperSize = xlPaperLetter
               .PrintTitleRows = "$1:$1"
               .PrintTitleColumns = "$A:$B"
           End With
       Next ws
    
       ThisWorkbook.PrintOut
   
   End Sub

   
4. In the Save Print Output As file dialog, name the pdf file and click on Save.
   

As a result, after opening the file, you will see an output like the following GIF.

OUTPUT GIF
​

I am attaching the solution workbook for better understanding. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[bigme]

dear Lutfor
a thousand, correction.... a million thank you for the solution

regards,
bigMe

 

[Lutfor Rahman Shimanto]

dear Lutfor
a thousand, correction.... a million thank you for the solution

regards,
bigMe

Dear BigMe

You're most welcome! I'm delighted to hear that the solution worked for you.

Warm Regards
Lutfor Rahman Shimanto
ExcelDemy