Changing Page Numbers

[karenbluez]

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.

        '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]

You simply do a page setup in your code, like:
ActiveSheet.PageSetup.FirstPageNumber = 6

 

[karenbluez]

Thank goodness it's that simple! Thanks man. Will give it a go.

 

[GlennUK]

Good luck!

 

[karenbluez]

Worked like a charm - thanks again!

 

[GlennUK]

My pleasure!