How do I print multiple worksheets without consecutive numbering?

[excel111]

Hi,

I cannot find the answer to this anywhere. I have an Excel with several worksheets. Some worksheets are multiple pages. Each worksheet is a distinct section of a report and will need to start at 1. E.g. Section A1, Section A2, Section B1, Section C1 would be 4 worksheets, but the first one is one two pages according to my page breaks.

Is there any way to print the entire workbook WITHOUT the page numbers going consecutively throughout the whole document? I would equate it to create a "Section Break" in Excel like you can in Mircrosoft Word.

If anyone can help me I would be very happy! I have about a dozen worksheets per book and a dozen workbooks, so printing tab by tab is tedious and I think that there must be a better way!

Thanks!!!

 

[Mohammad Basem]

Hi and welcome to the forum!

Does this help?

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub PrintWorkbookAsSections()
[COLOR="Green"]'*
'* Print Workbook considering each sheet as separate section with its own page numbers
'*[/COLOR]

    Dim aSection
    Dim iSectionElement As Integer
    Dim Wsh As Worksheet

    [COLOR="Green"]'*
    '* Specify sections' names
    '*[/COLOR]
    aSection = Array("Section 1", "Section 2", "Section 3", "Section 4", "Section 5")

    If UBound(aSection) + 1 < ActiveWorkbook.Sheets.Count Then
        MsgBox "Specified number sections is less than the number of sheets." & _
            vbNewLine & "Please fix and run again.", vbInformation, "Sections Mismatch"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    For Each Wsh In ActiveWorkbook.Sheets
        With Wsh.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = aSection(iSectionElement)
            .CenterFooter = ""
            .RightFooter = "&P of &N"
        End With
        Wsh.PrintOut
        iSectionElement = iSectionElement + 1
    Next Wsh
    Application.ScreenUpdating = True
    Set Wsh = Nothing

End Sub[/COLOR][/SIZE][/FONT]

 

[excel111]

Hi! Thanks for posting a response! It might work but I am not sure how to put in the code for this. Is it to create a macro? I am not THAT advanced in Excel... Is there any way you could give me instructions on how to add this code?

Thanks!

 

[Mohammad Basem]

Apologise for not explaining this earlier.

1. From the worksheet press Alt+F11 to open the Visual Basic Editor (VBE).
2. From the VBE Menu, select Insert -> Module. A new Module will be inserted and its code window will appear to the right.
3. Copy the code and paste in the Module code window.
4. Run the Macro PrintWorkbookAsSections from the macros dialogbox. You know how to do this, right?