Copying page and reference numbers to a new page

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,214,940
Messages
6,122,356
Members
449,080
Latest member
Armadillos

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
Back
Top