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.

[TABLE="width: 1108"]
<colgroup><col><col><col><col span="2"><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Strategy[/TD]
[TD]Project #[/TD]
[TD]Project Name[/TD]
[TD]GBU[/TD]
[TD]Pillar[/TD]
[TD]Lead[/TD]
[TD]Eval Score[/TD]
[TD]Sum of BP Capital[/TD]
[TD]Sum of LE Capital[/TD]
[TD]Sum of Act Capital[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]70[/TD]
[TD]Name 70[/TD]
[TD]GBU 1[/TD]
[TD]Source[/TD]
[TD]Joe[/TD]
[TD]3[/TD]
[TD]$985,000[/TD]
[TD]$985,000[/TD]
[TD]$985,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]34[/TD]
[TD]Name 34[/TD]
[TD]GBU 1[/TD]
[TD]Source[/TD]
[TD]Mark[/TD]
[TD]3[/TD]
[TD]$499,000[/TD]
[TD]$499,000[/TD]
[TD]$499,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]268[/TD]
[TD]Name 268[/TD]
[TD]GBU 1[/TD]
[TD]Source[/TD]
[TD]Mark[/TD]
[TD]3[/TD]
[TD]$499,000[/TD]
[TD]$499,000[/TD]
[TD]$499,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]202[/TD]
[TD]Name 202[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]160[/TD]
[TD]Name 160[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]175[/TD]
[TD]Name 175[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]189[/TD]
[TD]Name 189[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]93[/TD]
[TD]Name 93[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]235[/TD]
[TD]Name 235[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]113[/TD]
[TD]Name 113[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]1[/TD]
[TD]Name 1[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 1[/TD]
[TD]132[/TD]
[TD]Name 132[/TD]
[TD]GBU 1[/TD]
[TD]Plan[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Strategy 10[/TD]
[TD]241[/TD]
[TD]Name 241[/TD]
[TD]GBU 1[/TD]
[TD]Make[/TD]
[TD]Sally[/TD]
[TD]0[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[/TR]
[TR]
[TD]Strategy 10[/TD]
[TD]208[/TD]
[TD]Name 208[/TD]
[TD]GBU 1[/TD]
[TD]Make[/TD]
[TD]Sally[/TD]
[TD]0[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[/TR]
[TR]
[TD]Strategy 10[/TD]
[TD]7[/TD]
[TD]Name 7[/TD]
[TD]GBU 1[/TD]
[TD]Make[/TD]
[TD]Sally[/TD]
[TD]0[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[/TR]
[TR]
[TD]Strategy 10[/TD]
[TD]195[/TD]
[TD]Name 195[/TD]
[TD]GBU 1[/TD]
[TD]Make[/TD]
[TD]Sally[/TD]
[TD]0[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[TD]$134,500[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]80[/TD]
[TD]Name 80[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Joe[/TD]
[TD]1[/TD]
[TD]$1,120,000[/TD]
[TD]$1,120,000[/TD]
[TD]$1,120,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]182[/TD]
[TD]Name 182[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]209[/TD]
[TD]Name 209[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]100[/TD]
[TD]Name 100[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]196[/TD]
[TD]Name 196[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]120[/TD]
[TD]Name 120[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]242[/TD]
[TD]Name 242[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]139[/TD]
[TD]Name 139[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
[TR]
[TD]Strategy 11[/TD]
[TD]167[/TD]
[TD]Name 167[/TD]
[TD]GBU 2[/TD]
[TD]Deliver[/TD]
[TD]Steve[/TD]
[TD]1[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[TD]$148,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.