Select all pivot table areas/ranges on the active sheet problem
 

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

Select all pivot table areas/ranges on the active sheet problem
 

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

Select all pivot table areas/ranges on the active sheet problem
 

Hi!
Thanks for the word! I have pasted the corrected code below. I also have included a
an alternative procedure that does the same thing as well.

Sub PivotTablesSelectAllOnSheet()

'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
Else
Set rng = Union(rng, ActiveSheet.PivotTables(i).TableRange2)
End If
Next i
End With
rng.Select

End Sub


Sub PivotTableRangesSelectAll()
'Selects the ranges of all pivot tables on 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 = Selection
Else
Set rng = Union(rng, Selection)
End If
Next i
End With
rng.Select

End Sub



search criteria:
select all pivot tables select all pivot table areas
select all pivot table ranges return pivot table ranges to array
return pivot table areas to array create array from pivot table areas
create array from pivot table range
select all pivottables select all pivottable areas on active sheet
loop through pivot tables

Select all pivot table areas/ranges on the active sheet problem
 

what kind of utter nonsense post is this?
collecting brownie points from search engines?

not quoting my post
adding all the search criteria at the bottom

and STILL missing the point .. only SELECT when needed!

your first proc should definitely do WITHOUT the
line
.PivotTables(i).TableRange2.Select

your second proc does the same as the first..
but slower...
so what's the point?



keepITcool

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


(DataFreakFromUtah) wrote:

Hi!
Thanks for the word! I have pasted the corrected code below. I also
have included a an alternative procedure that does the same thing as
well.

Sub PivotTablesSelectAllOnSheet()

'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
Else
Set rng = Union(rng,
ActiveSheet.PivotTables(i).TableRange2)
End If
Next i
End With
rng.Select

End Sub


Sub PivotTableRangesSelectAll()
'Selects the ranges of all pivot tables on 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 = Selection
Else
Set rng = Union(rng, Selection)
End If
Next i
End With
rng.Select

End Sub



search criteria:
select all pivot tables select all pivot table areas
select all pivot table ranges return pivot table ranges to array
return pivot table areas to array create array from pivot table areas
create array from pivot table range
select all pivottables select all pivottable areas on active sheet
loop through pivot tables