CurrentRegion Property to Specify SourceData in PivotCache Method

jbrcks


I'm fairly new to writing VBA macros to create pivot tables in Excel, and I could use some help. I would like the macro I am writing to be able to use the Currentregion property in order to specify the SourceData for the PivotCache method, where the source data is located on a sheet named "macro". Any feedback warmly appreciated.

Jon Peltier

This syntax works:

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Worksheets("macro").Range("A1").CurrentRegion) _
    .CreatePivotTable TableDestination:=Worksheets("pivot").Range("B2"), _
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Note: It's pretty easy to figure these out. Record a macro to get the syntax:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/12/2006 by Jon Peltier
'
  Range("A1:C20").Select
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "macro!R1C1:R20C3").CreatePivotTable TableDestination:="[Book8]pivot!R2C2", _
    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
End Sub

then look up the properties or methods in help. Here's what help says about SourceData:

SourceData  Optional Variant. The data for the new PivotTable cache. This argument is required if SourceType isn't xlExternal. 
Can be a Range object, an array of ranges, or a text constant that represents the name of an existing PivotTable report. 
For an external database, this is a two-element array. The first element is the connection string specifying the provider of the data. 
The second element is the SQL query string used to get the data. If you specify this argument, you must also specify SourceType.

The blue sentence is all I needed. I set SourceData equal to a range object whose definition included CurrentRegion (Worksheets("macro").Range("A1").CurrentRegion), tested it, and posted it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

jbrcks

Thanks for your response. I'm new to VBA and also new to forums, so it's nice to find that the forum route works for help. I did have a bit of a follow up. Prior to your post, I was able to find a work around using the PivotTableWizard method, without using PivotCaches or specifying a range for the input data, using the following code:

ActiveSheet.PivotTableWizard TableDestination:=Range("Sheet1!A3"), TableName:="PivotTable1"

From what I can tell, this code appears to use whatever data is bounded by the active sheet as a default, thereby emulating the CurrentRegion property. I'm not sure what it's implications are with regard to PivotCaches. Might there be some unseen danger in using this approach as an alternative to using PivotCaches.Add and CreatePivotTable

Jon Peltier

I've adopted the following for creating a new pivot table:

 Set ptTable = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rData) _
   .CreatePivotTable(TableDestination:=wsPivot.Range("A3"), TableName:="PTPrice", 
   DefaultVersion:=xlPivotTableVersion10)

where ptTable is declared as a PivotTable, rData as an Excel.Range, and wsPivot as a worksheet. I know I had problems with the previous technique I'd been using, but the above eliminated the problems. I don't recall exactly what the previous technique had been.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______