Saving to PDF Repeating Rows With "Page X of X" in Excel

Question

I have an Excel file with repeating rows. Inside this repeating rows, I wanted to place "Page 1 of 2" and "Page 2 of 2", etc. into each page. I found this VBA code which worked fine when printing the file, but doesn't work when saving the file as PDF.

Sub PrintWithPgNumInTitleRow()
Dim NumPages As Long, pg As Long
    NumPages = ExecuteExcel4Macro("Get.document(50)")
    For pg = 1 To NumPages
        With ActiveSheet
            .Range("O10").Value = "Page " & pg & " of " & NumPages
            .PrintOut from:=pg, To:=pg
        End With
    Next pg
End Sub

When I changed my default printer to pdf writer, it requires me to input two file names (for two pages), but I wanted to have these two pages in one file only.

Answer 1

You get two Pdf-Docs, because you only print one page with

.PrintOut from:=pg, To:=pg

E.g. with 2 pagespg=1orpg=2

.PrintOut from:=1, To:=1 

and

.PrintOut from:=2, To:=2

But why change printer and provide a filename? Use ExportAsFixedFormat

Sub PrintWithPgNumInTitleRow()
Dim NumPages As Long, pg As Long 'skip all code, except last line (`ExportAs...`), and use Page-Setup
    NumPages = ExecuteExcel4Macro("Get.document(50)")
    With ActiveSheet
        For pg = 1 To NumPages
            .Range("O10").Value = "Page " & pg & " of " & NumPages

        Next pg
    '.PrintOut from:=1, To:=NumPages `solution for printer
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:=“path\to\doc.pdf”, _ 
                         Quality:=xlQualityStandard, DisplayFileAfterPublish:=True, _
                         From:=1, To:=NumPages 
    End With
End Sub

You can insert page numbers without code.