Copying Pivot Data to Table

[TheRedCardinal]

Hi experts,

I am new to VBA and very keen to learn but unfortunately I am working on a massive project at the same time so learning curve is steep.
I think it is normal to post attempts at code when asking for advice, but I have no idea where to start with this one.

I am basically working backwards from the end of the "project" to the start.

The final steps I am working on at the minute are:

• User finalises a set of data and manually adjusts as needed
• Pivot Table (which exists because of prior period workbook) is refreshed, using range called Data to ensure I capture the difference in the number of rows in the source data
• The data from the pivot table is copied to a new sheet with added extra data for each row, which is then exported to a CSV file

My question is getting the data from the Pivot Table into a usable format.

I have created a simple table on the same tab as the Pivot Table which has column headers in the exact same order as the Pivot Table has.

I have then made a macro which copies over the data from my table, into an existing tab which has the range ready for the CSV file.

I think this is the best way, as it allows me to manipulate the data as I need to. I can then just copy and paste the Pivot Data straight into my table, and calculate the 6th column.

The pivot table has 3 row fields, and 2 value fields. My table has 6 columns, the first 5 matching the layout of the pivot table, and a 6th column that is determined by a formula.

The final table has 10 columns, the extra 4 being populated based on formula.

So my questions are:

1. Using VBA how could I extract all the data (not headers) from my Pivot table, with enough scope to get all the data irrespective of how many rows it has
2. How to then paste that into my table in the right places
3. Is my table in the middle of this process unnecessary? If not, how can I extract the right columns into the right place on the new worksheet?

Thanks everyone!

 

[SpillerBD]

So my questions are:

1. Using VBA how could I extract all the data (not headers) from my Pivot table, with enough scope to get all the data irrespective of how many rows it has
2. How to then paste that into my table in the right places
3. Is my table in the middle of this process unnecessary? If not, how can I extract the right columns into the right place on the new worksheet?

Thanks everyone!

3. Probably. Since a Pivot Table usually summarises, you can LOAD the data using PowerQuery (now Get and Transform) and do your aggregate functions. Additionally, you can likely perform the additional calculations in the Query step. The PowerQuery can then load the results to a Table on a sheet, which would then be saved as a CSV.