Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
Pivot table active dropdown | Excel Worksheet Functions | |||
How to set printer default to select active sheet not whole book | Excel Discussion (Misc queries) | |||
How to select the active sheet? | Excel Worksheet Functions | |||
Select First Active Cell if the cell is in a pivot table | Excel Programming |