VBA to set page breaks

[Big Lar]

I assembled this code a while ago to create a printable recap report on 8.5 x 11 paper.
It almost works to produce my desired result…the exception:

Sheets(“Gross and Net”) will usually fill an entire sheet of paper.
But, Sheets(“G1-N3”) typically have only 20 lines max each.
Therefore, I must manually modify the Page Breaks and then print as PDF.

Any suggestions on how to set page breaks so that G1 & N1 will be printed on one page…
And ditto, G2 & N2
And, G3 & N3?

Sub MacroCreateRecap()
 
    myrange = Cells(Rows.Count, 22).End(xlUp).Address
   
    For Each ws In Sheets(Array("GROSS", "NET", "G1", "N1", "G2", "N2", "G3", "N3"))
      ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange
     
      Next ws
 
    Sheets("Recap").Select
   
    For Each ws In Sheets(Array("GROSS", "NET", "G1", "N1", "G2", "N2", "G3", "N3"))
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       
       Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
 
        ws.Range("A1:V" & LastRow).Copy _
            Destination:=Sheets("RECAP").Range("A" & Rows.Count).End(xlUp).Offset(2)
 
    Next ws
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
   
‘Clean up the formatting
Range("A1") = "ReCap"
        Columns("A:V").Select
         Selection.ColumnWidth = 3
          Columns("A").AutoFit
           Columns("V").AutoFit
             Rows("2").Select
             Selection.Delete Shift:=xlUp
           Range("A1").Select
         Range("U2").ClearContents
 
 
MyDateString = Format(ActiveSheet.Range("A3").Value, "yymmdd")
   
Dim s As String
    s = ActiveSheet.Range("A2")
Dim d As String
    d = MyDateString
 ActiveSheet.Name = d & " " & s
 
‘More code follows to print as pdf and save

 

[Yongle]

Excel is programmed to print separate sheets separately - non negotiable

So use VBA to
- create a tempoaray sheet (temp)
- paste whatever needs combining from 2 sheets to temp
- print temp
- delete temp

 

[Big Lar]

Thanks Yongle.
With a bit of code manipulation, I have resolved the issue

For Each ws In Sheets(Array("G1", "N1"))
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
       Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
 
        ws.Range("A1:V" & LastRow).Copy _
            Destination:=Sheets("RECAP").Range("A" & Rows.Count).End(xlUp).Offset(2)
 Next ws
 
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

Etc...Etc