Copy Pivot into Table - - headers going into Table body

Hi,

I need to copy a Pivot Table into an existing table. I have just about got everything working however, the copied range of the pivot table needs to be pasted into the Column headers of my table. That is "shifted by 1 row up". Does anyone perhaps have an eloquent way of doing this?

Q03 Copy pivot into table with VBA.xlsm

Capture.PNG

Thanks in advance,
Michelle

You can define your pivot data area as a named dynamic name and copy it into the excel table area.

Here his the definition for the named range: Pivot_Data =OFFSET(Sheet1!$G$7,0,0,COUNTA(Sheet1!$G:$G)-2,3)

I also included some code to clear out old data in a pivot table. It's one of my "standard" modules, so I just imported it.

Hi this is something I'm trying to leverage into my own file. Is there a way to expand the copy paste function to include headers created if I include a field under Columns in the pivot table. I basically have months broken into columns in my data. Take for instance in your Solution 2 in the pivot table if i were to drag the Profit field under Columns. Thanks