Chart object effect on Dim sht As Worksheet

[CinqueTerra]

I have a macro that refreshes Pivot Tables by looking for worksheets with
names beginning with HALGL and then refreshes pivot tables within those
sheets. The macro works great until I insert a chart object. Then the macro
is unable to evaluate:
For Each sht In ActiveWorkbook.Sheets
....
Next sht

Why is this? What is the solution???

Thanks, in advance, for your help!

My code:
Sub mcrRefreshPivot()
Dim sht As Worksheet
Dim pvt As PivotTable
Dim i As Integer
Dim pf As PivotField
Dim pi As PivotItem

'Turn calculation off during pivot refresh and month selection process.
Application.Calculation = xlManual

'Refresh the Main Pivot Tables.
'Since other pivot tables are based on the main table, this will refresh
them, as well.
On Error GoTo ErrorHandler

'Sheets("HALGL...").PivotTables("pvt_Ref...").RefreshTable

For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 5) = "HALGL" Then
For i = 1 To sht.PivotTables.count
Set pvt = sht.PivotTables(i)
If Left(pvt.Name, 7) = "pvt_Ref" Then
pvt.RefreshTable
End If
Next i
End If
Next sht

 

[Randy Harmelink]

You could try adding this IF statement:

If sht.Type = xlWorksheet then
end if

....so that the Chart sheet (Type = 4) is not processed.

 

[Andy Pope]

Hi,

Use the worksheet collection instead of the Sheets collection to
restrict the type of sheet.

For Each sht In ActiveWorkbook.WorkSheets
....
Next sht

You declared the variable sht as a worksheet object so the Chart sheet
would give a type mis-match error.
Also using the .Sheets collection will return all sheets within the
workbook. This would include worksheets and chart sheets.

Cheers
Andy

 

[CinqueTerra]

Awesome! Thanks!

Hi,

Use the worksheet collection instead of the Sheets collection to
restrict the type of sheet.

For Each sht In ActiveWorkbook.WorkSheets
....
Next sht

You declared the variable sht as a worksheet object so the Chart sheet
would give a type mis-match error.
Also using the .Sheets collection will return all sheets within the
workbook. This would include worksheets and chart sheets.

Cheers
Andy