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

bigme

Member
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
 
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.
Dataset to apply page layout setup and print all sheets.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.
    Code:
    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
    Paste the following code and Run.png
  4. In the Save Print Output As file dialog, name the pdf file and click on Save.
    Name the PDF file and Save.png
As a result, after opening the file, you will see an output like the following GIF.
I am attaching the solution workbook for better understanding. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 

Attachments

  • BigMe (SOLVED).xlsm
    30.3 KB · Views: 1

Online statistics

Members online
1
Guests online
19
Total visitors
20

Forum statistics

Threads
318
Messages
1,408
Members
583
Latest member
mibr fan token
Top