CurrentRegion Property to Specify SourceData in PivotCache Method

CurrentRegion Property to Specify SourceData in PivotCache Method

• GeoffSeattle

  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

  
  

• Zak1968

  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
  _______

  
  
  

• LeahGarrett

  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
  _______

  
  
  
• CurrentRegion Property to Specify SourceData in PivotCache Method