VBA Code for different headers on first page from other pages of same worksheet

[Rewound]

Hi All,

First time poster so apologies if this isn't right formatting.

I have seen this question be asked a lot, but having tried all the variety of options, none of them seem to be working.

I have a worksheet which, depending on the value of 1 cell I want to print either 1 or 2 pages - have added this bit, its working, all good.

My issue is, that when 2 pages are selected I need a slightly different header on each of the pages (because we duplex print) which refer to specific cells . Clearly it is possible in Excel 2010, because for standard options (date/pages etc) it's there. But I can't seem to work this out either than by recording and editing macros, or from searching t'internet. - my VBA knowledge is limited!

Neither code works properly but the first is better than the 2nd for what I need. The first code forces the print to be on separate pint commands, which I'd like to avoid and the second not all headers update.

The bit of code that I think needs amendment is this bit (full code for the sub at the end of the post):

---

Case Else
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8") & vbLf & "&12" & "Investment"
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
ActiveSheet.PageSetup.LeftHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = "&b" & "&12" & ActiveSheet.Range("$h$9")
ActiveSheet.PageSetup.RightHeader = ""
ActiveSheet.PrintOut From:=2, To:=2

---

Also tried (header on first or 2nd page doesn't update/change/stays blank):

---

Case Else
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.OddAndEvenPagesHeaderFooter = True
.EvenPage.LeftHeader.Text = "&b" & "&16" & ActiveSheet.Range("$h$8")
.EvenPage.CenterHeader.Text = "&b" & "&12" & ActiveSheet.Range("$h$9")
.EvenPage.RightHeader.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = "&b" & "&16" & ActiveSheet.Range("$h$8") & vbLf & "&12" & "Investment"
End With
Application.PrintCommunication = True
ActiveSheet.PrintOut From:=1, To:=2, Preview:=True
With ActiveSheet.PageSetup
.OddAndEvenPagesHeaderFooter = True
.EvenPage.RightHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.LeftHeader.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
End With
End Select

---

Any help gratefully received!

Thanks

R

Full code for Macro:

---

Sub PrintForms()
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer
Dim Partial As String

Sheets(" Form").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")
Partial = Range("Partial")

For i = StartRow To EndRow
Range("RowIndex") = i
If Range("Preview") Then
Select Case Range("Partial")
Case Is = "y"
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1, Preview:=True
Case Is = "Y"
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1, Preview:=True
Case Else
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8") & vbLf & "&12" & "Investment"
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1, Preview:=True
ActiveSheet.PageSetup.LeftHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = "&b" & "&12" & ActiveSheet.Range("$h$9")
ActiveSheet.PageSetup.RightHeader = ""
ActiveSheet.PrintOut From:=2, To:=2, Preview:=True
End Select
Else
Select Case Range("Partial")
Case Is = "y"
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
Case Is = "Y"
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
Case Else
ActiveSheet.PageSetup.RightHeader = "&b" & "&16" & ActiveSheet.Range("$h$8") & vbLf & "&12" & "Investment"
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PrintOut From:=1, To:=1
ActiveSheet.PageSetup.LeftHeader = "&b" & "&16" & ActiveSheet.Range("$h$8")
ActiveSheet.PageSetup.CenterHeader = "&b" & "&12" & ActiveSheet.Range("$h$9")
ActiveSheet.PageSetup.RightHeader = ""
ActiveSheet.PrintOut From:=2, To:=2 End Select
End If
Next i
End Sub

---