• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1586
  • Last Modified:

VBA Copy a Pivottable over itself values and format only

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.
0
shipleyp
Asked:
shipleyp
  • 3
  • 2
1 Solution
 
redmondbCommented:
shipleyp,

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

Please replace it by the following...

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

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

Regards,
Brian.
0
 
shipleypAuthor Commented:
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.Copy
                .TableRange2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End With

Thanks
Phil.
0
 
redmondbCommented:
Phil,

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.

Regards,
Brian.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
shipleypAuthor Commented:
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 :)
0
 
redmondbCommented:
Glad to help, shipleyp. The main reason I'm on this site is that I'm learning a lot too!
0
 
moi4557Commented:
Hi Guys,

Can you paste the entire vba codes please ?

Thanks

Thierry
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now