Changing Page Numbers

karenbluez

New Member
Joined
Apr 29, 2009
Messages
18
I have spreadsheet with various sheets that print as a book.
So sheet1 will be the Cover, sheet2 the Index, sheet3 the first report, etc.
Each sheet is not 1 page. The page numbers have to follow on each other.
So I've found a macro to calculate how many pages there are per sheet.

Code:
        'i is the row numbers where the sheet names are stored.

        For i = 45 To 64
        MySheet = Worksheets("Settings").Cells(i, 3).Value
        Worksheets(MySheet).Activate
        ActiveWindow.View = xlPageBreakPreview

        Worksheets(MySheet).DisplayAutomaticPageBreaks = True
        HorizBreaks = Worksheets(MySheet).HPageBreaks.Count
        HPages = HorizBreaks + 1
        VertBreaks = Worksheets(MySheet).VPageBreaks.Count
        VPages = VertBreaks + 1
        NumPages = HPages * VPages
        Worksheets(MySheet).DisplayAutomaticPageBreaks = False

        Worksheets("Settings").Cells(i, 4) = NumPages
        
    Next i
This code then insert the number of pages on the Settings sheet, where I can calculate the first page number of each sheet, example Sheet4 is page number 12.

I want to know if there is a way to read from the Settings sheet these numbers and insert them in this Print dialogue box per sheet... in the First Page Number block which is currently on Auto.



Uploaded with ImageShack.us


I need the correct page numbers to show, so if I only print out Report 2 which starts on page 5 and is 5 pages long, Page 5 needs to be at the bottom of the first page of Report 2 and Page 10 on the last page of Report 2.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
You simply do a page setup in your code, like:
ActiveSheet.PageSetup.FirstPageNumber = 6
 

Watch MrExcel Video

Forum statistics

Threads
1,118,050
Messages
5,569,891
Members
412,298
Latest member
dietitiann
Top