Using VBA to copy data from pivot and paste into defined ranges on another sheet. Idea is to copy all columns in pivot for each change in first pivot

[JmanRueng]

Hi All, (sorry for wordy post)

A VBA newby, but I've tasted the CoolAid and I know how powerful it can be so I'm trying to improve my skills.

In short, I'm trying to create a process to pull select data from a pivot table and copy it to a specific range on another sheet.

The pivot table has 10 columns (7 row labels, 3 values) and the first column row label is title "Strategy". I've setup the pivot so that the top 10 projects are displayed for a given strategy, however sometimes there aren't 10 projects per strategy but instead only 1, or 3, or 5, for example...

There are 36 Strategies in the Pivot.

Ideally what I'd like to do is automate a process that copies - for every change in the 1st column (strategy) - the nine columns to the right, and populates 36 corresponding 9X10 tables/named ranges on another excel tab.

I know how to copy an entire pivot and paste into a specific location using VBA, but I wanted to avoid making 36 pivot tables if I could. Any help would be appreciated!

I mocked up a workbook as well, if easier (although I'm not sure how to share this!)

Thanks!

Example below...Strategy 1 has 12 (but I only want first 10), Strategy 10 has 4 rows, and Strategy 11 has 9 rows. I would want the macro to for every change in strategy copy the data and paste in corresponding data table (named by the same strategy) on another sheet.

Strategy	Project #	Project Name	GBU	Pillar	Lead	Eval Score	Sum of BP Capital	Sum of LE Capital	Sum of Act Capital
Strategy 1	70	Name 70	GBU 1	Source	Joe	3	$985,000	$985,000	$985,000
Strategy 1	34	Name 34	GBU 1	Source	Mark	3	$499,000	$499,000	$499,000
Strategy 1	268	Name 268	GBU 1	Source	Mark	3	$499,000	$499,000	$499,000
Strategy 1	202	Name 202	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	160	Name 160	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	175	Name 175	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	189	Name 189	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	93	Name 93	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	235	Name 235	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	113	Name 113	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	1	Name 1	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 1	132	Name 132	GBU 1	Plan	John	0	$50,000	$50,000	$50,000
Strategy 10	241	Name 241	GBU 1	Make	Sally	0	$134,500	$134,500	$134,500
Strategy 10	208	Name 208	GBU 1	Make	Sally	0	$134,500	$134,500	$134,500
Strategy 10	7	Name 7	GBU 1	Make	Sally	0	$134,500	$134,500	$134,500
Strategy 10	195	Name 195	GBU 1	Make	Sally	0	$134,500	$134,500	$134,500
Strategy 11	80	Name 80	GBU 2	Deliver	Joe	1	$1,120,000	$1,120,000	$1,120,000
Strategy 11	182	Name 182	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	209	Name 209	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	100	Name 100	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	196	Name 196	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	120	Name 120	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	242	Name 242	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	139	Name 139	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000
Strategy 11	167	Name 167	GBU 2	Deliver	Steve	1	$148,000	$148,000	$148,000

<colgroup><col><col><col><col span="2"><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Jerry Sullivan]

A VBA newby, but I've tasted the CoolAid and I know how powerful it can be so I'm trying to improve my skills.

Be careful, this CoolAid can be addicting.

Stepping back to look at the big picture, it might be more direct to just pull your data directly from your data source to the 36 tables instead of having the intermediate Pivot.

Is there any reason that you need or want the Pivot? For example, if you need to dynamically change other filters through the Pivot and have those changes reflected in the tables, that might be good interface; however if there are no filters or static filters, then you could go right to the tables.

 

[JmanRueng]

Hi JS411, Interesting suggestion. My rawdata is significantly more complex - 163 columns with 37 additional formula columns, and over 8000 rows of data. That said, I could easily write a macro to copy the pivot and paste it as values on another sheet so my "new raw data" would be exactly the information that I need. The last step would be writing some code to copy columns/rows of data for every change in the first column strategy and then to paste in corresponding strategy named range...any thoughts?

 

[JmanRueng]

I'm still interested to see what everyone says, but I found one potential solution (partial) from another thread. The idea is to use the autofilter function to copy.

Sub FilterCopy()
Application.ScreenUpdating = False


[allrawdata].AutoFilter Field:=1, Criteria1:="x"
[selectedrawdatacolumns].Copy Sheets("DataTable").[StrategyTable1]
[A1:C10].AutoFilter
Application.ScreenUpdating = True
End Sub

This will move the right data to the right location, but I have one snare - it moves all data. I only want to move 10 rows worth...the first ten. And even though I'm using a top 10 filter, sometimes there are more than 10 rows...when there's a tie based on filter value. Any suggestions for this (or other more efficient approaches)?

I could write 36 segments of the same code for each strategy...assuming i fix the top 10 issue.

 

[Jerry Sullivan]

When I suggested pulling your data directly from the Raw Data to the 36 reports I was thinking of using querries. That can be done using Query Tables with MS Query or ADO through VBA code.

That would be similar the approach you were considering of applying an AutoFilter to the Raw Data and copying Visible Records. A key difference would be that with a Query you can Aggregate (Sum, Count, Average) your data, which can't be done by AutoFilter-Copy-Paste alone.
If you are listing the Top 10 projects and each project has only 1 unique record, then Aggregating isn't required..but even then a query is a good approach to consider.

A Query would be made using SQL which would allow you to apply a TOP 10 clause to filter out only the Top 10 aggregated results.
The TOP 10 would return no more than 10 items. If there are ties, the query simply truncates the items greater than the Top 10.

I'd be glad to help if you'd like to pursue that approach.