VBA for different footers on different sheets in one workbook

[Pierce Quality]

Hi All,

I was wondering if anyone could offer some assistance on this problem:
I have one workbook.
The workbook has 20 sheets.
The VBA is placed in the "WorkBork" section.
I only want the VBA to activate on the specific sheets outlined in the code.
The VBA is set to display a center footer with the text in 12 point Verdana
The text displayed is the active sheet name then "My text here" then the value of cell B2 (which is a date)
All the other sheets not listed have specific static headers.
The code works fine when just individual sheets are printed.

When the entire workbook is printed however, the VBA footer from Sheet1 is displayed across all the other sheets footers listed in the code instead of the individual sheet's footer.

Please Help!
Thanks!

Sub Workbook_BeforePrint(Cancel As Boolean)

With Sheet1.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet5.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet4.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet6.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet8.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet7.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet9.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet10.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet11.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet12.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  ActiveSheet.Name & " My text here " & Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

End Sub

[6StringJazzer]

In each case, change

Range("B2").Text

to

.Range("B2").Text

That is, add a dot in front, which means "Use the Range for the sheet specified in the With statement." Otherwise you are using the range in the active sheet throughout.

[Pierce Quality]

In each case, change

Range("B2").Text

to

.Range("B2").Text

That is, add a dot in front, which means "Use the Range for the sheet specified in the With statement." Otherwise you are using the range in the active sheet throughout.

Thanks 6StringJazzer,
When I just added the dot I got a 'run time error 438'.
I ended up changing the activesheet.name portion to the specfic sheet number, then added the specific sheet number to the .Range portion and it seems to work perfectly now.
Thanks!

Heres the end code:

Sub Workbook_BeforePrint(Cancel As Boolean)

With Sheet1.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet1.Name & " My text here " & Sheet1.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet5.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet5.Name & " My text here " & Sheet5.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet4.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet4.Name & " My text here " & Sheet4.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet6.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet6.Name & " My text here " & Sheet6.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet8.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet8.Name & " My text here " & Sheet8.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet7.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet7.Name & " My text here " & Sheet7.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet9.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet9.Name & " My text here " & Sheet9.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet10.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet10.Name & " My text here " & Sheet10.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet11.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet11.Name & " My text here " & Sheet11.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

With Sheet12.PageSetup
.CenterFooter = _
  "&12&""Verdana""" & _
  Sheet12.Name & " My text here " & Sheet12.Range("B2").Text
  B2 = Format(Date, " DD MMMM ")
End With

End Sub

[6StringJazzer]

Oh, sorry, I didn't notice that your With statements also included .PageSetup rather than just the sheet. Glad that I was able to help.

[Pierce Quality]

Oh, sorry, I didn't notice that your With statements also included .PageSetup rather than just the sheet. Glad that I was able to help.

Thanks, appreciate the help!