VBA Code

[Hayton]

Hi Guys,

I need some VBA code that says:
If an error exists to skip the procedure and move onto the next condition or else run the following bit of code

The Code is:
Sheets("CPK Manipulation").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.CenterHeader = "&""TAHOMA,Bold""&28CPK DATA"
End With

I know that this is simple but I know very little about coding.

Many Thanks

Hayton McGregor

 

[SkipVought]

Hi,

Please post code questions in VBA Visual Basic for Applications (Microsoft) Forum707.

  on error resume next
...
'some statement that raises an error
  select case err.number
    case 0
      'do this if there is no error
    case nSomeOtherErrNbr
      'do this of ther error number is nSomeOtherErrNbr
    case else
      '???bail out???
  end select
  err.clear

Skip,

_[red][/red]

 

[Hayton]

Hi Skip,

Apologies for taking so long to reply to your post.

Skip in the above example 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 templte. We hide some sheets because a customer mught 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 activating 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]

"Please post code questions in Forum707"

Skip,

_[red][/red]

 

[Hayton]

I have posted in VBA Forum, Title 'Looping Through Active Worksheets.

Apolgies for not doing so in the first place.

Hayton McGregor