I have a workbook with about 30 worksheets in it. This workbook is used for a reporting tool for a customer(s). So we have many reports generated from the same template. We hide some sheets because a customer might not require it in his report.
So for each worksheet I have a snippet of vba that populates the Header & Footer with some information etc. I run the code by clicking a button
Private Sub cmdInsert_Click()
' places string and graphics into header/footer of all worksheets
' make sure that the path for the image is correct
' run once a year at the rollover from one year to the next
Dim ws As Worksheet
' designated path for image
ActiveSheet.PageSetup.LeftHeaderPicture.Filename = _
"T:\Customer Reporting\CPK Template\Graphics for template\Bstlogo.jpg"
' set parameters and loop through worksheet to load text and images
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Inserting header/footer data in " & ws.Name
With ws.PageSetup
' run this to insert graphics and text into header footer
.LeftHeader = "&G"
.CenterHeader = ""
.RightHeader = "&""Tahoma,Regular""&8Ver: 06.05"
.LeftFooter = "&""Tahoma,Regular""&8&A" & Chr(10) & "&F"
.CenterFooter = "&""Tahoma,Regular""&8&P of &N" & Chr(10) & _
"© Bridgestone New Zealand Ltd - " & Year(Now())
.RightFooter = "&""Tahoma,Regular""&8&D"
End With
Next ws
Sheets("Maintenance").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.CenterHeader = "&""TAHOMA,Bold""&28MAINTENANCE"
End With
...
Set ws = Nothing
Application.StatusBar = False
ActiveWorkbook.Save
' forces workbook to open at worksheet named Dashboard
Sheets("Dashboard").Select
End Sub
What I am trying to acheive is that this, instead of having yards of code, the code should do the above for all unhidden worksheets.
Currently I have a snippet of code for each worksheet and an error occurs when I run the code and a worksheet is hidden.
Any suggestions would be appreciated.
Many Thanks
Hayton McGregor
So for each worksheet I have a snippet of vba that populates the Header & Footer with some information etc. I run the code by clicking a button
Private Sub cmdInsert_Click()
' places string and graphics into header/footer of all worksheets
' make sure that the path for the image is correct
' run once a year at the rollover from one year to the next
Dim ws As Worksheet
' designated path for image
ActiveSheet.PageSetup.LeftHeaderPicture.Filename = _
"T:\Customer Reporting\CPK Template\Graphics for template\Bstlogo.jpg"
' set parameters and loop through worksheet to load text and images
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Inserting header/footer data in " & ws.Name
With ws.PageSetup
' run this to insert graphics and text into header footer
.LeftHeader = "&G"
.CenterHeader = ""
.RightHeader = "&""Tahoma,Regular""&8Ver: 06.05"
.LeftFooter = "&""Tahoma,Regular""&8&A" & Chr(10) & "&F"
.CenterFooter = "&""Tahoma,Regular""&8&P of &N" & Chr(10) & _
"© Bridgestone New Zealand Ltd - " & Year(Now())
.RightFooter = "&""Tahoma,Regular""&8&D"
End With
Next ws
Sheets("Maintenance").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.CenterHeader = "&""TAHOMA,Bold""&28MAINTENANCE"
End With
...
Set ws = Nothing
Application.StatusBar = False
ActiveWorkbook.Save
' forces workbook to open at worksheet named Dashboard
Sheets("Dashboard").Select
End Sub
What I am trying to acheive is that this, instead of having yards of code, the code should do the above for all unhidden worksheets.
Currently I have a snippet of code for each worksheet and an error occurs when I run the code and a worksheet is hidden.
Any suggestions would be appreciated.
Many Thanks
Hayton McGregor