dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I have 2 cells on my spreadsheet that I want on each subsequent page. One cell has a reference number and one cell has the page number/s. I want the cells both copied below when a page break is inserted. The page numbers is an excel function so copying it will work.
On the first page, the reference number is in H2:I2 and the page number is in I41, which is almost at the top and bottom of the page. When a new page is created, the range A8:V32 is copied and placed at the start of the new page. The rows before row 8 are relating to the entire document so they don't need to be copied.
This is the code that is run when another page is made:
What else do I need to add so the reference number and page number is copied in the same position as on the first page to the top and bottom of new pages that are inserted?
On the first page, the reference number is in H2:I2 and the page number is in I41, which is almost at the top and bottom of the page. When a new page is created, the range A8:V32 is copied and placed at the start of the new page. The rows before row 8 are relating to the entire document so they don't need to be copied.
This is the code that is run when another page is made:
VBA Code:
Sub AddRows()
Dim WS As Worksheet, lr As Long
Set WS = ThisWorkbook.Worksheets("ACA_Quoting")
Call Move_Shape
With WS
lr = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A8:V32").Copy .Range("A" & Rows.Count).End(xlUp).Offset(16, 0) 'Pastes a copy of the table below current table between the bottom of the table and the totals
.Range("A" & Rows.Count).End(xlUp).Offset(16, 0).PageBreak = xlPageBreakManual
'copy and paste offset by 40 rows
End With
End Sub
What else do I need to add so the reference number and page number is copied in the same position as on the first page to the top and bottom of new pages that are inserted?