Copy pivot table values, format, and style

[RiaM]

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!

 

[RiaM]

Bump

Please. Is anybody out there ...

 

[RoryA]

You could just copy the whole table to the new book, then copy and paste special-values the new table.

 

[RiaM]

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

 

[RoryA]

Which version of Excel? When I do that in 2003, the colours remain as they were. How are you applying the styles?

 

[RiaM]

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"

 

[RoryA]

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.

 

[RiaM]

Thanks again!