Excel 2007 : Paste Special Values for Pivot Tables

[PaulTaylor]

This is throwing a wrench in my entire reporting process. In 2003, I have a number of sheets in a workbook all pivot tables referring to another table. This will typically be 30mb or so. To distribute, I'll copy/paste special values only, then the resulting file will be <1mb and they don't have to be linked to the source table.

In 2007, copy/paste special will remove the formatting from the pivot table. I figure this is an easy fix, I just need to find out how this is done in 2007. After some googling, I'm worried this actually might not be possible. I don't know what to do, short of printing and sending as pdf which my users won't like. Manually reformatting the tables to make them look pretty is infeasible.

What do I do? Please help!

[Paul]

Hi Paul, and welcome to the forum.

Two quick options...

1. Use PasteSpecial -> Formats, then PasteSpecial -> Values (two steps, pretty quick)
2. Add the following code to a module in your personal.xls file (or an add-in) and then attach it to a toolbar button:

Please Login or Register  to view this content.

The code will do both steps with one click on the button. Be sure to select the destination location first, though!

Hope that helps.

[PaulTaylor]

Hi Paul, and welcome to the forum.

Two quick options...

1. Use PasteSpecial -> Formats, then PasteSpecial -> Values (two steps, pretty quick)
2. Add the following code to a module in your personal.xls file (or an add-in) and then attach it to a toolbar button:

Please Login or Register  to view this content.

The code will do both steps with one click on the button. Be sure to select the destination location first, though!

Hope that helps.

Thanks.

I tried this already. The paste special wipes out the pivot table formatting leaving you with a ***** table of data.

[PaulTaylor]

Thanks.

I tried this already. The paste special wipes out the pivot table formatting leaving you with a ***** table of data.

lol, it makes it sound like I was saying something crude or something. I said it was a n a k e d table of data. I didn't mean to sound pornographic or anything.

[PaulTaylor]

No one can help with this? Don't tell me this is really impossible in Excel 2007.

[broro183]

Hi Paul,

This may be of no help at all since it is based on Excel 2003 but I'm sure I have seen something similar on the Excel 2007 ribbon too...

Provided all the PT's are based on the same source data table, try right clicking on each of the pivot tables & making them "share the same pivot cache" by pressing the Back button on the PT wizard until it asks what you want to base the PT on - choose "another PT", select the first PT in the list & click Finish. Repeat for all PT's using the same PT choice each time. This means that the PT's are based on a single "shared cache" rather than having an individual copy of the data in memory for each PT.

See Debra's site for code examples:
http://contextures.com/xlPivot11.html

hth
Rob

[PaulTaylor]

Hi Paul,

This may be of no help at all since it is based on Excel 2003 but I'm sure I have seen something similar on the Excel 2007 ribbon too...

Provided all the PT's are based on the same source data table, try right clicking on each of the pivot tables & making them "share the same pivot cache" by pressing the Back button on the PT wizard until it asks what you want to base the PT on - choose "another PT", select the first PT in the list & click Finish. Repeat for all PT's using the same PT choice each time. This means that the PT's are based on a single "shared cache" rather than having an individual copy of the data in memory for each PT.

See Debra's site for code examples:
http://contextures.com/xlPivot11.html

hth
Rob

Thanks. Yes, I do that already to keep my Excel files from getting too large. But this doesn't have anything to do with the formatting. Thanks for the reply anyway.

[PaulTaylor]

OK, here's a lead if someone can help me from here.

If I copy the sheet with the pivot table and open a new workbook, I can do a paste special and get new options, one of which is to paste as html. This appears to do the trick, preserving values and formatting (except for column widths) and removing the pivot table. However, I can't figure out how to do this in vba. I'm not a vba expert. I do the record macro and some modification, but that's about the extent.

I get the code

Selection.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False

This is the critical code, apparently. But when I try to do this within the same worksheet, it fails on me. I can do it manually by opening a new workbook, but I don't know how to use vba to do it systematically, which I'll need to do because I'll have multiple sheets per workbook where I'm doing this.

Help?

[broro183]

hi Paul,

Can you please upload an example workbook with a tiny amount of dummy data (just enough to make a PT work) & all of your recorded code?
This provides some more context for us to help with - and if necessary (ie we can't modify it for the same file) we can provide looping code to create all the formatted values from the PT's into a new file (& delete the original/save with a new name etc).

Rob

[PaulTaylor]

hi Paul,

Can you please upload an example workbook with a tiny amount of dummy data (just enough to make a PT work) & all of your recorded code?
This provides some more context for us to help with - and if necessary (ie we can't modify it for the same file) we can provide looping code to create all the formatted values from the PT's into a new file (& delete the original/save with a new name etc).

Rob

Please Login or Register  to view this content.

As for the spreadsheet, just imagine ten tabs. One with data, nine with pivot tables reading off the data tab (or reading off external source...doesn't matter).

It's just a simple paste special values only. I add the for next to loop through all the tabs.

[broro183]

Paul,

The code in your last post (#10) is different from the code in your previous post (#8). Can you please include the entire code which was recorded when you got the snippet posted in #8?

I can easily imagine such a file but we are trying to help you - you can make it easier by helping us to help you... please upload a sample file.

btw, I'm currently at a computer using Excel 2000, so the more context you can provide the better (if it ends up being me who helps you).

a few of questions:
- Are you still working in Excel 2007?
- Looking back at Post # 2, does it make any difference if you change the order by pasting the formats before the values?
- Are you opening the "new workbook" in the same Instance of Excel?
- What happens if you paste special the value tables below the PT's rather than on top of them?
- Or even paste special into new sheets within the existing file?

hth
Rob

[PaulTaylor]

Paul,

The code in your last post (#10) is different from the code in your previous post (#8). Can you please include the entire code which was recorded when you got the snippet posted in #8?

I can easily imagine such a file but we are trying to help you - you can make it easier by helping us to help you... please upload a sample file.

btw, I'm currently at a computer using Excel 2000, so the more context you can provide the better (if it ends up being me who helps you).

a few of questions:
- Are you still working in Excel 2007?
- Looking back at Post # 2, does it make any difference if you change the order by pasting the formats before the values?
- Are you opening the "new workbook" in the same Instance of Excel?
- What happens if you paste special the value tables below the PT's rather than on top of them?
- Or even paste special into new sheets within the existing file?

hth
Rob

Thanks. On the copy/save as html, I found my answer. Instead of copy/paste special in a new Excel instance as html, I found an easier solution to save the entire workbook as html, then reopen and save as .xlsx.

I'd like to do this copy/paste special values solution I've always used. To answer your questions:

1. yes in 2007. This works fine in previous versions. This is why I have the issue. My pattern for building numerous reports is to build pivot tables then wipe them clean with a copy/paste special values. Previous versions would retain the formatting.

2. No, it doesn't matter the order.

3. The save as htm only worked in a new instance of Excel, at least as an option when you right click, but I'm past that issue. I'd like to do the copy/paste special values within the same spreadsheet.

4. Doesn't work.

5. Doesn't work.

[broro183]

Great, I wasn't much help but I'm pleased you have found your answer.

Can you please mark this thread as solved?

Rob

[PaulTaylor]

I'd still like to know a solution on how to copy/paste special values for pivot tables to preserve formatting. This is the preferred method for me. Very strange that I could do this in 2003 and not in 2007.

[broro183]

hi Paul,

Yes, version differences can be strange (if this is what it is?). Have you had any other changes made to your setup (Windows/Office/Clipboard) recently?

No promises, but you may find some useful info on one of the links on the below page: http://peltiertech.com/Excel/Pivots/pivotstart.htm

hth
Rob

[PaulTaylor]

hi Paul,

Yes, version differences can be strange (if this is what it is?). Have you had any other changes made to your setup (Windows/Office/Clipboard) recently?

No promises, but you may find some useful info on one of the links on the below page: http://peltiertech.com/Excel/Pivots/pivotstart.htm

hth
Rob

Nothing there that would help. And no nothing has changed in my setup.

It looks like others are asking this same question with no luck...

http://forums.techguy.org/business-a...ot-tables.html

[Mr. Excel]

I got stuck on this for a while too and the internets where no help.

1. Copy the pivot table (entire sheet will work)
2. Open a new sheet or workbook to paste the data into
3. Home > Clipboard > click on the clipboard item

that will paste the values and the formats.