Insert Cell Value of Second Row of Column A after each Page Break as the Right Header

Status
Not open for further replies.

Riccosuave

New Member
Joined
Apr 29, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Okay, I found this bit of code from a post back in 2010, and I am hoping that somebody can please help me figure out what kind of variable "hb" should be, and what it should be pointing to because the original person that posted this code never included them at all. I know this code was intended to do what I want, but I cannot for the life of me figure out how to complete it. I have included a link to the original post, and I am really hoping somebody can help me here.

Original Post: excel vba change header at each page

What I am ultimately trying to do is change the header that is printed to the cell value of the second row of Column A every time there is a page break.

So, for example:
Pages (1-10)
Cell Value (A2) = Trees
So Right Header on Page (1-10) Should = Trees

Page Break

Pages (11-20)
Cell Value (Value of Second Row in Column A after the Page Break) = Buildings
So Header on Pages (11-20) Should = Buildings

Here is the code that I have. All I really need is to know what "hb" should be defined as and pointing to I guess? I hope I am asking this right.

VBA Code:
Sub Insert_Headers()
Dim hb As ? '(I need to know what this should be)
hb = ? '(I need to know what this should be too)

'The first page
ActiveSheet.PageSetup.RightHeader = Cells(ActiveSheet.HPageBreaks(1).Location.Row - 2, 1).Value
counter = 1

'to get the other pages and manipulate them
For Each hb In ActiveSheet.HPageBreaks
counter = counter + 1
ActiveSheet.PageSetup.RightHeader = Cells(hb.Location.Row, 2).Value
Next hb
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Status
Not open for further replies.

Forum statistics

Threads
1,136,800
Messages
5,677,809
Members
419,721
Latest member
StuckInWork

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