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.
 

GlennUK

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

Forum statistics

Threads
1,082,555
Messages
5,366,293
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top