VBA Copy a Pivottable over itself values and format only

Posted on 2011-10-25
Last Modified: 2012-05-12
I have a function which generates pivot tables within Excel from Access. For each sheet with a pivot table I want to select it and paste it over itself.  I can find plenty of examples of how to copy using TableRange2, but these all then paste to a new location.

I determine the location of the pivot table by first reading the next empty cell in column 1 after having entered a simple list also based upon an access query, then generate the pivot based on data in another sheet in the same workbook (whihc came from a function in access) and then tweak the format of the pivot a little. Then I want to copy and paste the pivottable to remove the fact it is a pivot so that I can then perform more formating and i also need to replace the field values with an 'x' in the body of the now fake pivot.  I thought I could use the previously determined location reference as the paste location, but it is not working. TransExport.bas PivotCode.bas

Getting a 'Unable to get the PageRange property of the pivotTable class' error 1004.
Question by:shipleyp
    LVL 26

    Expert Comment


    I presume that the problem is with the following line...
    TableRange2.Copy Destination:=xlWSh.Range(myRange2)

    Please replace it by the following...

    .tablerange2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    This will "delete" the Pivot table, leaving its data and formatting intact.


    Author Comment

    Hi Brian,

    Worked a treat, so I did not need to know the cell reference of the pivot to paste it, but is there an easy way to capture the range of the pivot before it is pasted so i can use this range for my furhter formatting.

    I changed the section of code to:

                Set myRange2 = xlWSh.Range("A1").End(xlDown).Offset(1, 0)
                rngAddr = myRange2.Address(False, False, xlR1C1)
                strPvtName = "PivotTable" & strFilter & ""
                strDataSrc = strSheetName & "_1!R2C1:R50000C12"
                strTblLoc = strFilter & "!" & rngAddr & ""
                Call PivotGenerate(strDataSrc, strTblLoc, strPvtName, xlWBk)
                With xlWSh.PivotTables(strPvtName)
                    .PivotFields("Value").DataRange.Orientation = 90
                    .TableRange2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                End With

    LVL 26

    Accepted Solution


    I did not need to know the cell reference of the pivot to paste it

    The address of .TableRange2 is exactly that. Of course, once the pivot is gone TableRange2 is no longer valid, so you might add the following before the Copy...

    Dim Old_Pivot_Range As Range
    Set Old_Pivot_Range = .TableRange2

    Old_Pivot_Range will then be available for you after the Pivot is gone.


    Author Comment

    Thanks Brian, it dawned on me after i posted back that Table range is a range in itself and no need to try a .address or anything like that. I am a newbie at vba, but learning so much, you have been a great help. question answered :)
    LVL 26

    Expert Comment

    Glad to help, shipleyp. The main reason I'm on this site is that I'm learning a lot too!

    Expert Comment

    Hi Guys,

    Can you paste the entire vba codes please ?



    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now