Designing page numbers to ignore certain pages based upon a condition

[Laurence D]

Hi all,

I want to add page numbers to my workbook to make life easier when printing around 800 pages. Does anybody know how to design a page numbering code to ignore adding page numbers to specific pages based on a certain condition? The condition that I am requiring is that it ignore all pages that have a blank cell on the fourth cell down of a new page (there are three headers). I still want to be able to print these pages it is just unnecessary to have them numbered.

Currently I have code that adds numbers to all pages but I am unsure how to add this step in.

Any help is greatly appreciated

Thanks,
Laurence

 

[footoo]

Try this :

Sub PageNbr_Print()
Dim pb As HPageBreak, c%, p%, cel As Range
p = 1
With ActiveSheet
    Set cel = ActiveCell
    Cells(Rows.Count, Columns.Count).Select
    .PageSetup.RightFooter = ""
    If [A4] = "" Then
        .PrintOut From:=1, To:=1
        c = 2
   Else
        .PageSetup.RightFooter = "Page 1"
        .PrintOut From:=1, To:=1
        c = 2
        p = 2
    End If
    For Each pb In .HPageBreaks
        If Range(pb.Location.Address).Offset(3) = "" Then
            .PageSetup.RightFooter = ""
            .PrintOut From:=c, To:=c
            c = c + 1
        Else
            .PageSetup.RightFooter = "Page " & p
            .PrintOut From:=c, To:=c
            c = c + 1
            p = p + 1
        End If
    Next
    cel.Select
End With
End Sub

If you make amendments to the code, bear in mind this : https://support.microsoft.com/en-us...ut-of-range-error-message-when-you-use-hpageb

 

[Laurence D]

Thanks Footoo,


Does not quite work right for me but it is a start and your help is appreciated! The 800 pages worth of printing are on about 30 different worksheets in one workbook (important detail I forgot to add sorry) and I need the code to loop through all of the worksheets. I have begun removing the printout code as I will do that without code when the code is working 100%. The last thing I am trying to develop is to make the pages be numbered Page 1 of 2 etc. The total page number is the last page on each worksheet.

 

[footoo]

It's not entirely clear what you want.
For example, given the following :
page 1 - 4th cell not blank
page 2 - 4th cell blank
page 3 - 4th cell not blank

Do want the page numbers to be :
page 1 - Page 1
page 2 - Blank
page 3 - Page 2

Or like this :
page 1 - Page 1
page 2 - Blank
page 3 - Page 3

I have begun removing the printout code as I will do that without code when the code is working 100%.

Re the red highlight, I think you need to do it with code since the footer needs to be reset for each page.

The last thing I am trying to develop is to make the pages be numbered Page 1 of 2 etc. The total page number is the last page on each worksheet.

To get the total pages for each sheet (i.e. including those with no page number footer):

ActiveSheet.PageSetup.Pages.Count

 

[Laurence D]

Thanks Footoo,

Sorry I now understand the concept of detail. I have already gone through all 800 pages to add in page breaks to make every worksheet of my workbook evenly numbered (so I can print the entire workbook rather than a worksheet). When data in one of my worksheets only makes up an odd page (e.g. Page 1 of 1), the next page below will be blank on the 4th cell down. Not every worksheet will have a blank on the 4th cell down as half of the worksheets are randomly evenly numbered. This is what I want my page numbers to look like:

Worksheet1:
page 1 4th cell not blank = Page 1
page 2 4th cell not blank = Page 2

Worksheet2:
page 1 4th not blank = Page 1
page 2 4th not blank = Page 2
page 3 4th not blank = Page 3
page 4 4th is blank = not numbered

Worksheet3:
page 1 4th not blank = Page 1
page 2 4th is blank = not numbered

Worksheet4:
page 1 4th not blank = Page 1
page 2 4th not blank = Page 2
page 3 4th not blank = Page 3
page 4 4th not blank = Page 4