Page Number in cell

[John117]

Hello
I have a template spanning between Col “A” and Col “DH”. This template is populated with data from TXT files. Sometimes I need to print only 1 page and other times lets say 8 pages, which depends on amount of data I am importing into this template.
Here is my code to print the correct # of pages:

Private Sub Worksheet_Calculate()

Select Case Range("num_pages").Value
Case 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$45"
Case 2
ActiveSheet.PageSetup.PrintArea = "$A$1:$X$45"
Case 3
ActiveSheet.PageSetup.PrintArea = "$A$1:$AI$45"
Case 4
ActiveSheet.PageSetup.PrintArea = "$A$1:$AT$45"
Case 5
ActiveSheet.PageSetup.PrintArea = "$A$1:$BE$45"
Case 6
ActiveSheet.PageSetup.PrintArea = "$A$1:$BP$45"
Case 7
ActiveSheet.PageSetup.PrintArea = "$A$1:$CA$45"
Case 8
ActiveSheet.PageSetup.PrintArea = "$A$1:$CL$45"
Case 9
ActiveSheet.PageSetup.PrintArea = "$A$1:$CW$45"
Case 10
ActiveSheet.PageSetup.PrintArea = "$A$1:$DH$45"
End Select
End Sub

Does anybody knows how to display in cell (not using header / footer function) page number, i.e. “Page 1 of 2” if I am printing only 2 pages or “Page 1of 8” if I am printing lets say 8 pages.
Thanks
John

 

[John117]

Bump

 

[XLGibbs]

YOu can add the desired result to a cell using your case select construct based on the following example:

Case 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$45"
ActiveSheet.Range("m45") = "Page 1 of 1"
Case 2
ActiveSheet.PageSetup.PrintArea = "$A$1:$X$45"
ActiveSheet.Range("M45") = "Page 1 of 2"
ActiveSheet.Range("X45") = "Page 2 of 2"


Assuming you wanted the page 1 of ? to be in the bottom right of the printed area.

Why not just use page footer and use the #page of #pages method? You would not have to add any code or update it every time, as it only applies to printed sheets which is covered by your Print Area code .

 

[John117]

Gibbs
Thank you very much. That did the trick.

John

 

[Nimrod]

Hi John :

If your interested , here's a method that would allow you to get rid of your case statement.

Private Sub Worksheet_Calculate()
    Dim PrintRng As String

    PrintRng = Range("A1").Address & ":" & _
    Cells(45, 13 + (11 * (Range("num_pages").Value - 1))).Address
    
    ActiveSheet.PageSetup.PrintArea = PrintRng


End Sub