Different Header on Each Page

[NEM25]

I have a spreadsheet with warehouse locations in the first column in an Excel worksheet. For each location there can be one or more items. Items are listed in the second column. I need to print out all of this information, with each location on a different page. I have used the following Macro to achieve this:

Sub pagebrk()
col = 1
LastRw = ActiveSheet.UsedRange.Rows.Count
For x = 2 To LastRw
If Cells(x, col) <> Cells(x - 1, col) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(x, col)
End If
Next
End Sub

If possible, I would like to put the location as the header on each page that is printed out. I have tried to find a suitable macro to do this, but so far have failed.

Any advice would be much appreciated.

Cheers,

NEM.

 

[texasalynn]

This will setup a header by a cell range, so maybe this:

ActiveSheet.PageSetup.LeftHeader = _
Format(Worksheets("Sheet2").Range(Cells(x, col)).Value)

put this in the section after "Then"

 

[NEM25]

Thanks for the suggestion texasalynn. However, when I try it, I get a runitime error '1004' (application-defined or object defined error). This is what the macro now looks like:

Sub PageBreak_and_Header()
col = 1
LastRw = ActiveSheet.UsedRange.Rows.Count
For x = 2 To LastRw
If Cells(x, col) <> Cells(x - 1, col) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(x, col)
ActiveSheet.PageSetup.LeftHeader = _
Format(Worksheets("Test Page Breaks").Range(Cells(x, col)).Value)
End If
Next
End Sub

Any further suggestions?

Thanks,

NEM.

 

[texasalynn]

a made a few changes and it seemed to work

Sub PageBreak_and_Header()
Dim col As Long, x As Long
Dim ws As String
col = 1
LastRw = ActiveSheet.UsedRange.Rows.Count
For x = 2 To LastRw
If Cells(x, col) <> Cells(x - 1, col) Then
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(x, col)
    ws = ActiveSheet.Name
    Sheets(ws).PageSetup.LeftHeader = ActiveSheet.Cells(x, col)
End If
Next
End Sub