Copying page and reference numbers to a new page

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,033
Office Version
  1. 365
  2. 2016
Platform
  1. 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:
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top