Copy pivot table values, format, and style

RiaM

Board Regular
Joined
Jun 5, 2009
Messages
67
Hi

I've written macros to create, format, and select a style (background colours, font colours etc.). Then I wrote a macro to copy the pivot table values and format (number formatting) across to a new worksheet. However I'm struggling to copy across the styles (colour backgrounds) across? I want to use the new sheet as a static report. Any ideas?

Thanks!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You could just copy the whole table to the new book, then copy and paste special-values the new table.
 
Upvote 0
You could just copy the whole table to the new book, then copy and paste special-values the new table.

Hey Rory
Nope, that get's the values and number format's across. Not the background styles/colours. I tried pasting everything at first, and it works but still a pivot table. Then I paste.special values only, as soon as I do this the colour backgrounds dissappear - I dont understand why. Its as if the style/colour is applicable when its a PT only.

Other ideas?

I've thought of manually formatting the table when its pasted in a new sheet, but would much rather stay away from this as the PT automatically adjusts heading colours and so as e.g. when headings go across two rows. I am working on this option though
 
Upvote 0
Which version of Excel? When I do that in 2003, the colours remain as they were. How are you applying the styles?
 
Upvote 0
Excel 2007.

Styles applied in macro, but essentially what its doing is selecting the pivot table, then going into the design tab (top of screen) and selects one of the default Pivot Styles

ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleMedium10"
 
Upvote 0
Ah, I don't have 2007 here to test, but it would make sense that a PivotTable style wouldn't be applied to a normal range. I suspect you may have to manually format the table.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top