Looping through Active Worksheets 1

[Hayton]

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

 

[SkipVought]

Hi,

Store the sheet visible property and then make the sheet visible, process and restore

...
    For Each ws In ActiveWorkbook.Worksheets
        Application.StatusBar = "Inserting header/footer data in " & ws.Name
        
        'store the sheet visible property
        vVisible = ws.Visible
        'make the sheet visible
        ws.Visible = xlSheetVisible
        
        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
        
        'restore the sheet visible property
        ws.Visible = vVisible
        
    Next ws
...

Skip,

_[red][/red]

 

[SkipVought]

Not sure what you mean by, "...the code should do the above for all unhidden worksheets."

Are you not doing this For Each ws in Worksheets?


Skip,

_[red][/red]

 

[Hayton]

Skip, each Worksheet in the Workbook Template represents an element of the report. We hide some of the worksheets (ie the customer does not need that element in the report)

Sheets("Maintenance").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.CenterHeader = "&""TAHOMA,Bold""&28MAINTENANCE"
End With

So for the above snippet of code we have a worksheet called Maintenance. If I hide this sheet and run the code it gives me a error becuase of the page being hidden. Currentl I will comment this code so that it does not run.

Each of the the worksheets will run the above code. I have listed this code say 30 times, each 'mini module' with its own name (Sheets("Insert work sheet name").Select

So what I am trying to do, is to have one bit of code that loops through all the sheets that are unhidden and run the above process for each sheet.

Hayton McGregor

 

[SkipVought]

Is that not what I posted for you?

Skip,

_[red][/red]

 

[Hayton]

Skip, when I run your code, it does not insert into the centre pane of the header, the name of the worksheet. The name of the worksheet is the name designated to the the tab of the worksheet.

Otherwise the code works well.

Many Thanks

Hayton McGregor

 

[SkipVought]

Did you macro record using the [ignore]&[Tab][/ignore] in that part of the header?

Skip,

_[red][/red]

 

[Hayton]

Skip, I have got it all working. Thanks for your help.
Cheers

Hayton McGregor

 

[SkipVought]

Good for you!

Skip,

_[red][/red]