try something like this:

Dim wrksht As Worksheet
Dim pivtble As PivotTable
Dim pivotCounter As Integer

pivotCounter = 0

For Each wrksht In ActiveWorkbook.Worksheets

For Each pivtble In wrksht.PivotTables

If Right(pivtble.Name, 1) pivotCounter Then
pivotCounter = Right(pivtble.Name, 1)
End If

Next pivtble

Next wrksht

pivotCounter = pivotCounter + 1


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:="Sheet1!R25C10:R30C12").CreatePivotTab le TableDestination:=
_
"[Book2]Sheet1!R36C10", TableName:="PivotTable" & pivotCounter,
DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable" & pivotCounter).AddFields
RowFields:="a"
ActiveSheet.PivotTables("PivotTable" &
pivotCounter).PivotFields("c").Orientation = _
xlDataField

Pete


--
Peter81
------------------------------------------------------------------------
Peter81's Profile: http://www.excelforum.com/member.php...o&userid=25353
View this thread: http://www.excelforum.com/showthread...hreadid=531446