code is almost ok.. except

instead of assigning a RANGE object to the union you try it with an
address string..

If rng IS nothing
Set rng = _
ActiveSheet.PivotTables(i).TableRange2
Else
Set rng = _
Union(rng,ActiveSheet.PivotTables(i).TableRange2)
End if

hth!

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(DataFreakFromUtah) wrote:

Greetings all!

Excel 2000 & Windows 2000
I've been working on this for several hours and have searched the
archive with no apparent luck.
What I'm trying to do is return all the ranges (TableRange2) of 1..nth
pivot tables
on an active sheet to an array. Then the procedure selects the range
represented by the array.

I keep getting Run Time error 424 object required. I think the error
refers to
ActiveSheet.PivotTables(i).TableRange2.Address. But this is a valid
range reference as far
I know. Also, I would like this procedure to work on sheets where the
number and names of the
pivot tables are not known.

Any thoughts?
Thank you for any insight you may provide.
Tom


Sub SelectAllPivotTableAreas()

'Select all pivot table areas on the active sheet

Dim i As Integer
Dim rng As Range
On Error Resume Next

With ActiveSheet
For i = 1 To .PivotTables.Count
.PivotTables(i).TableRange2.Select
If rng Is Nothing Then
Set rng =
ActiveSheet.PivotTables(i).TableRange2.Address
Else
Set rng = Union(rng,
ActiveSheet.PivotTables(i).TableRange2.Address)
End If
Next i
End With
rng.Select

End Sub