View Full Version : Solved: Presence of a Chart Sheet causes VBA to erro

07-24-2009, 12:00 PM
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?



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

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
'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

Set sht = Nothing

Application.ScreenUpdating = True

End Sub

07-24-2009, 12:10 PM
Sheets and charts are different collections.

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

07-24-2009, 12:11 PM

'Do all
For Each sht In Sheets


'Do all
For Each sht In Worksheets

07-24-2009, 12:21 PM
That would be a better suggestion :D

07-24-2009, 12:26 PM
Excellent, Thanks...