Speeding up a VBA macro for printing headers

[robwood83]

Hello All,

I have a Macro I am using to apply a custom header to certain sheets in a workbook, only about half the sheets. This is what I am using.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet1").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet2").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet2").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet3").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet3").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet4").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet4").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet7").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet7").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet10").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet10").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet12").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet12").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet15").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet15").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
    Worksheets("Sheet17").PageSetup.LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
    Worksheets("Sheet17").PageSetup.RightHeader = Sheets("Turn-In").Range("V28").Value
   
End Sub

This is working great but seem to really slow Excel down, is there any other options to accomplish this same thing but without the memory drag?

 

[p45cal]

I'm not sure it'll speed it up because thes page setup things can take a while sometimes but:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet7", "Sheet10", "Sheet12", "Sheet15", "Sheet17")).Select
Sheets("Sheet1").Activate
With ActiveSheet.PageSetup
  .LeftHeader = "& PETAP# " & Sheets("Turn-In").Range("G28").Value
  .RightHeader = Sheets("Turn-In").Range("V28").Value
End With
End Sub

I don't think you can avoid selecting for these purposes.

 

[robwood83]

That is kind of what I thought, thank you for the help though.