excel vba change header at each page

[planetexcel]

Is there a way to pass the top left cell value of each page to the header?

Thanks in advance,

Stelios

 

[Steeviee]

Try this:

Sub try
dim x as integer
dim hdr as string
x=1
do until x=worksheets.count+1
sheets(x).activate
hdr=cells(1,1).value
With ActiveSheet.PageSetup
.CenterHeader = hdr
End With
x=x+1
loop
end sub

 

[planetexcel]

Dear friend i am talking about printing one worksheet with different header in each page. Which it will be each page top left cell.

 

[Steeviee]

Hello.

If you paste the macro that I have written below into your workbook, then run it, you shoudl I think get what you want.

i will go through the process:

Sub try
dim x as integer    [COLOR=seagreen] 'sets up a variable to go through each worksheet[/COLOR]
dim hdr as string    [COLOR=seagreen]'sets up a variable to hold the title you want in the header
[/COLOR]x=1                    [COLOR=seagreen] 'starts with first sheet in your workbook[/COLOR]
do until x=worksheets.count+1          [COLOR=seagreen]'starts a loop for all of your sheets[/COLOR]
sheets(x).activate       [COLOR=seagreen]'activates sheet x (1st sheet then looping)
[/COLOR]hdr=cells(1,1).value    [COLOR=seagreen] 'picks up the value you have in the top left of that sheet and assigns it to variable hdr
[/COLOR]With ActiveSheet.PageSetup
.CenterHeader = hdr      [COLOR=seagreen]'sets the header to whatever is assigned to[/COLOR] [COLOR=seagreen]variable hdr[/COLOR]
End With
x=x+1                   [COLOR=seagreen]' adds one to x so that it can look at the next sheet in your workbook
[/COLOR]loop                     [COLOR=seagreen]'loops to go through the process again with next sheet[/COLOR].
end sub

I have tested this and it definitely looks at whatever is in cell A1 of each sheet and then puts that into the header.

 

[planetexcel]

What I was looking and after hard searching was the following..


Sub Prt()
'The first page
ActiveSheet.PageSetup.RightHeader = "Department : " & Cells(ActiveSheet.HPageBreaks(1).Location.Row - 1, 5).Value
ActiveSheet.PrintOut From:=1, To:=1
counter = 1
'to get the other pages and manipulate them
For Each hb In ActiveSheet.HPageBreaks
counter = counter + 1
ActiveSheet.PageSetup.RightHeader = "Department : " & Cells(hb.Location.Row, 5).Value
ActiveSheet.PrintOut From:=counter, To:=counter
Next hb
End Sub

Thanks for the try.