Finding all pivottables in a workbook using for each loop - 424 error

[bachstrad]

Hi all,

I'm new here, so forgive me if any formatting isn't right the first time!

I hope you guys can help me out with the following issue:

I'm trying to find all pivottables in a workbook using the following code. The goal is to instruct the found pivottables further on, but first I need to find them. The problem is the following code produces a 424-error message when the "for each" loop is started.

This is the code I used:

[FONT=courier new]Sub FindMyPTs()
Dim pt As PivotTable
Dim ptNames() As String
Dim ptName As String
Dim i As Integer

i = -1
For Each pt In PivotTables
i = i + 1
ReDim Preserve ptNames(i)
ptNames(i) = pt.Name
Next pt

For i = 0 To UBound(ptNames)
ptName = ptName & ptNames(i) & Chr(13)
Next i

MsgBox ptName
End Sub[/FONT]

What am I doing wrong? Can anyone please help me out!

 

[Andrew Poulsom]

Welcome to MrExcel.

PivotTables is a member of the Worksheet object. So you need to loop around the worksheets and the pivot tables on them.

 

[bachstrad]

Thanks! That worked