PDA

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



JimS
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?

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
07-24-2009, 12:10 PM
Sheets and charts are different collections.

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

xld
07-24-2009, 12:11 PM
Change



'Do all
For Each sht In Sheets


to



'Do all
For Each sht In Worksheets

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

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