View Single Post
Old 01-20-2022, 05:13 AM
Matt C's Avatar
Matt C Matt C is offline Windows 10 Office 97-2003
Advanced Beginner
Join Date: May 2021
Location: London, UK
Posts: 30
Matt C is on a distinguished road
Question "BeforePrint" VBA causes only first worksheet of grouped worksheets to be printed

Hi folks.

I'm trying to suss out some VBA which automatically populates/updates the header/footer areas at the "before print" stage for Sheet 3 of a three-sheet workbook, of which Sheets 2 and 3 must be printed together.

Sheet 3 draws header/footer info from some defined cells in Sheet 1. Sheet 1 and 2's headers/footers are left blank. Sheets 2 and 3 must be printed together but when I group them for printing, only Sheet 2 is printed.

I also need to future-proof it for any new worksheets that are added later, which should also contain the same header/footer info and print when grouped with Sheets 2 and 3.

Can anyone help?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False

With Sheets(3)

'Left Header
.PageSetup.LeftHeader = Sheets(1).Range("HeaderLeft").Value

'Centre Header
.PageSetup.CenterHeader = Sheets(1).Range("HeaderCentre").Value

'Left Footer
.PageSetup.LeftFooter = Sheets(1).Range("FooterLeft").Value

'Centre Footer
.PageSetup.CenterFooter = Sheets(1).Range("FooterCentre").Value

'Right Footer
.PageSetup.RightFooter = Sheets(1).Range("FooterRight").Value

End With

Application.ScreenUpdating = True

End Sub
Reply With Quote