I found the following code from Ron de Bruin and have used it with good success until today when I needed to use it with Pivot Tables in Excel 2010. Here is the code from http://www.rondebruin.nl/copy6.htm:
When using this on a workbook with 30 sheets (all with a pivot table on them) the macro hangs up on this section:
In searching the internet, it seems the issue is with the Pivot Table and trying to copy/paste values. So I came across the following macro that is able to copy the Pivot Table as values, which works fine if I do the worksheets one at a time and save them. From http://www.contextures.com/excel-vba...te-format.html:
I was hoping to find a way to incorporate this macro into Ron de Bruin's macro through a Call argument.
Doing so works, but each subsequent worksheet overwrites the previous one when saving, and doesn't use the worksheet's name from the original workbook.
Does anyone know of a macro that can accomplish this task, or can you provide any guidance on how to modify the two above macros to do the job? My VBA knowledge is very limited and I haven't been able to figure out where to begin with this one. Both of these macros are much more complex than what I would normally create myself.
I've created a sample file with some fake names and information that may help you to visualize what I'm trying to do. By the end I'd like to have 4 workbooks named Penn State, Pitt, U Penn, Syracuse. The actual file has about 30 sheets instead of 4 and a lot more data.
Thanks in advance for any help you can provide.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks