Solved: Presence of a Chart Sheet causes VBA to erro

[JimS]

I have Template Workbook that uses several macros to import some data sheets from other workbooks, and then do some calculations on these newly imported data sheets.

The imported files come in and land on sheets that all begin with the name "Sheet" {ie: Sheet1, Sheet1 (2), Sheet1 (3), Sheet1 (4), etc}.

This code all worked fine until I added a few Chart Sheets in the Template Workbook.

For some reason now that there are Chart Sheets in the workbook this vba fails.

The Debug is sitting on the "Next sht" line.

I have some other macros in this Template that look at whether a sheet name begins with "Sheet" {If Left(.Name, 5) = "Sheet" Then} these also fail.

Any ideas?

Thanks...

Jim


Sub CalSheets()
Dim LastCol As Long
Dim LastRow As Long
Dim sht As Worksheet

'Speed
Application.ScreenUpdating = False

'Do all
For Each sht In Sheets
With sht
'Check name
If Left(.Name, 5) = "Sheet" Then
On Error Resume Next
'Last used Column
LastCol = .Cells.Find(What:="*", After:=.Cells(1, 1), _
lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
'Insert 10 rows
.Range("A1:A4").EntireRow.Insert
'Last used Row
LastRow = .Cells.Find(What:="*", After:=.Cells(1, 1), _
lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
On Error GoTo 0
'Column A labels
.Cells(1, 1) = "Max:"
.Cells(2, 1) = "Average:"
.Cells(3, 1) = "Percentile (.95):"
'Column B formulas
.Cells(1, 2) = "=MAX(B6:B" & LastRow & ")"
.Cells(2, 2) = "=Average(B6:B" & LastRow & ")"
.Cells(3, 2) = "=Percentile(B6:B" & LastRow & ", 0.95)"
.Range("B1:B3").Copy .Range(Cells(1, 3).Address, Cells(3, LastCol).Address)
End If
End With
LastRow = 0
LastCol = 0
Next sht

'Cleanup
Set sht = Nothing
'Reset

Application.ScreenUpdating = True

End Sub

[mumin_abdul]

Sheets and charts are different collections.

Dont declare sht as worksheet. I think itll still work that way.

[Bob Phillips]

Change



'Do all
For Each sht In Sheets


to



'Do all
For Each sht In Worksheets

[mumin_abdul]

That would be a better suggestion :D

[JimS]

Excellent, Thanks...