Limit header page numbers to only certain pages

[Milos]

Hi all,

Does anyone know if there would be a way to reshape or remove page setup headers that will exclude the last page of an active sheet? I currently have this macro inputting text "Page not used" when the data in each worksheet is oddly numbered. This makes an empty "dummy page" (as I am calling it) which converts all odd numbered worksheets evenly numbered. Printing the entire workbook with this macro is thus easy.

Example: In Sheet 3 I only have 5 pages worth of data but the macro below adds a sixth dummy page. What I am after is a way to stop the header for the dummy page so that it will only number pages 1-5 etc.

Could anybody let me know if this is even possible? My internet searches lead me to believe that it may not be...

Sub Page_Not_Used ()
'Button 6
Dim lr As Long, ws As Worksheet


Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets
      With ActiveSheet.PageSetup
            .RightHeader = "&P of &N"
      End With
            
     With ws
        .Activate
        If .PageSetup.Pages.Count Mod 2 = 1 Then
            lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
            .HPageBreaks.Add Before:=Rows(lr + 1)
            .Cells(lr + 1, 1).Value = "Page not used" 
        End If
    End With
Next ws


Application.ScreenUpdating = True


End Sub

Cheers,
Milos

 

[Yongle]

Would this work for you ?
(may cause problem if printing double-sided etc???)

SheetX
- insert NEW "SheetX" into your workbook
- SheetX comprises one cell with value "Page not used"
- SheetX has no headers and no footers

Printing
- do not add a dummy page break
- amend your printing code to print SheetX when Printed Sheet's PageSetup.Pages.Count Mod 2 = 1

 

[Milos]

Thanks Yongle that was a great idea I do not know why I never thought of that! I tried what you said and it has worked (partly). For some reason my entire workbook now seems to be printing with sequential page numbers rather than non-sequential (e.g. 1, 2, 3, 4, 5, 6 vs. 1,2,3 & 1,2,3). I will make a new thread to sort that out...