macro to add addtional pivot tables after the first pivot table

[Pete]

Question: Hi Experts

Need a macro to do the following:-

1. Add a second or may be third or forth pivot table after the first pivot table has already been added in row A3 "3" cell down from the Grand Total "text" in column A

2. The "Grand Total" text in column A can be any where in column A any cell depending on how big the pivot table is.

So the each time i add a pivot table the next one is three cell down in column A after the text "Grand Total" text in column A.

I have already completed the pivot table macro.....NOW just need to add them in sequence of end results.

[Pete]

ok i have the macros to do each of the pivot tables just needs the macro part to insert the next one three rows down from the previous pivot table based on column A having Grand Total.

This is the part i cannot do.............

[JWhite]

Are you saying that you want to put multiple versions of the same pivottable on the same sheet with each new one just below the last one?

That would be dangerous if they were refreshed and the number of rows changed when they refreshed because if the pivottable got longer it might encroach on the one below it and you'll get error messages. Maybe that wouldn't be a problem for you but it's safer to put them on different sheets or side-by-side since the number of columns won't change on a refresh.

The actual copying is easy. Using "YourPivottableObject.TableRange2" you can find the range of your pivottable and copy it anywhere. FYI, it's not well-documented but if you have multiple copies of the same pivottable (that is, they're using the same pivotcache) you only need to refresh one and they all refresh.

[Pete]

hi. Jwhite

its not the same pivot table but different pivot tables......on the refresh and adding more rows.....would it not be possible to aways keep 3 rows between one pivot table and the next based on the grand total text in column A. use this as an anchor????

[Bob Phillips]

Something like this



Dim rng As Range

With ActiveSheet.PivotTables("PivotTable1")

Set rng = .TableRange1

.PivotCache.CreatePivotTable _
TableDestination:=rng.Offset(rng.Rows.Count + 3, 0).Cells(1, 1).Address(, , xlR1C1, True), _
TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
End With

[Pete]

to the rescue xld...............as always

[Bob Phillips]

Pete,

I had no idea how to do what you asked when you asked, but by simply examing the pivot table object model in the watch window I was able to see some properties that might be it, a simple bit of experimentation and I ound a solution.

That sort of thing comes with the familiarity that comes with experience, no great skill, so we can all get there :)

[JWhite]

What's going to happen if the top table is refreshed and ends up 4 rows longer than before? Aren't you going to get an error message when it tries to overwrite the table below it? Or maybe that will never happen in this particular application.

I've accidentally put data in cells near a pivottable without allowing for possible expansion on a refresh and it won't refresh unless it has enough non-blank rows and columns to expand into.

[Bob Phillips]

Of course you will, it won't refresh as you say.

Presumably, Pete's data is constrained, otherwise when it is updated, he will need to ensure there are enough rows to accomodate the growth.