Macro -- Copying Pivot Table and Changing Row Labels

**andcarro** · 12-03-2013, 03:05 PM

Hola! Using Excel 2007 on Windows 7 64 bit. Familiar with using macros, but no idea how to write my own VBA -- only make very minor edits to existing code.

I attempted to record a macro to copy a pivot table with many row labels, multiple times on the same sheet. I then want to remove all but one Row Label, then repeat until I had individual tables with only one row label each. The issue is that this macro refers to each pivot table by its name statically (PivotTable148) and does not work when moving to a new sheet. How can I reference them dynamically?

Example code from my recorded macro below. I need the bold items to be updated dynamically. Or a totally new way to approach this

Please and thank you! I'd be happy to PayPal a cup of coffee to anyone who can help! I'll need to do this regularly in my new position.

Please Login or Register  to view this content.

**andcarro** · 12-04-2013, 08:31 AM

Bump --- any help deeply appreciated.

**Izandol** · 12-04-2013, 08:40 AM

Perhaps you can try:

Please Login or Register  to view this content.

**andcarro** · 12-04-2013, 08:45 AM

Thank you very much for your response. I'll give it a shot!

**andcarro** · 12-04-2013, 09:00 AM

I get the message [RUN TIME ERROR '1004': "PasteSpecial method of Worksheet Class failed) with this being the problem line:

Please Login or Register  to view this content.

**Izandol** · 12-04-2013, 09:02 AM

Is it same error using the original version instead:

Please Login or Register  to view this content.

**andcarro** · 12-04-2013, 09:53 AM

It is, unfortunately :/

**Izandol** · 12-04-2013, 09:55 AM

Is I2 empty?

**andcarro** · 12-04-2013, 11:44 AM

It is. However excel will paste the first table before giving me the error.

**Izandol** · 12-04-2013, 12:42 PM

If the first table is pasted I am not seeing how the line returns an error? Is it possible to provide a workbook for review?

**andcarro** · 12-04-2013, 02:25 PM

I'll try and get something with false data tomorrow. Thank you again for all of your help.

Macro -- Copying Pivot Table and Changing Row Labels

LinkBack

Thread Tools

Rate This Thread

Display

Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Re: Macro -- Copying Pivot Table and Changing Row Labels

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Changing dates in a Pivot Table using a Macro

Excel 2007 : Pivot Table Row Labels and Column Labels contain items not in data

Filtering data in a pivot table by changing column labels?

Excel 2007 : macro for changing pivot table criteria

changing Pivot table name via macro

Bookmarks

Bookmarks

Posting Permissions