VBA to create Pivot Tables using muliple Source Data and creating a new sheet

[zaincmt]

Hi All,

I am having a bit of an issue trying to create a pivot table in VBA.

Basically I run a report which creates a new workbook with a few tabs. I need a new bit of VBA code to add onto that code to create a Pivot table using the "All" worksheet in the newly created workbook to create a new worksheet and then create a pivot table using the data from the "All" worksheet.

The code I have so far is;

Function CreatePivotTable()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)

'Create a new worksheet
  Set sht = Sheets.Add

'Where do you want Pivot Table to start?
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")


  'Set up the row & column fields
    Pivot_sht.AddFields RowFields:=Array("Area", "Gp Br", "Ecars2Ticket-Reservation", "Car Status"), ColumnFields:="Office"
    
  'Add item to the Report Filter
    Pivot_sht.PivotFields("Area").Orientation = xlPageField
    Pivot_sht.PivotFields("Gp Br").Orientation = xlPageField
    Pivot_sht.PivotFields("Ecars2Ticket-Reservation").Orientation = xlPageField
    Pivot_sht.PivotFields("Car Status").Orientation = xlPageField
  
  'Add item to the Column Labels
    Pivot_sht.PivotFields("Car Status").Orientation = xlColumnField
    
  'Add item to the Row Labels
    Pivot_sht.PivotFields("Area").Orientation = xlRowField
    Pivot_sht.PivotFields("Gp Br").Orientation = xlRowField

   'Add Value to pivot table
    Pivot_sht.AddDataField Pivot_sht.PivotFields("Ecars2Ticket-Reservation"), "Count of Ecars2Ticket-Reservation", xlSum


  'Position Item in list
  '  Pivot_sht.PivotFields("Year").Position = 1
    
  'Format Pivot Field
  '  Pivot_sht.PivotFields("Year").NumberFormat = "#,##0"
    
  'Turn on Automatic updates/calculations --like screenupdating to speed up code
    Pivot_sht.ManualUpdate = False

End Function

However when I run the code it doesn't select the data from the "All" worksheet and when it adds a new worksheet it calls it "Sheet9" rather than "PivotTab".

Also it comes up with an error on this section;

'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")

I was wondering if any of you could help me please. Thank you in advance.

Kind Regards
Zain!

 

[Worf]

Hi

Sub CreatePivotTable()
Dim sht As Worksheet, pvtCache As PivotCache, pvt As PivotTable, StartPvt$, SrcData$
'Determine the data range you want to pivot
SrcData = Sheets("All").Name & "!" & Range("A1:g100").Address(ReferenceStyle:=xlR1C1)
Set sht = Sheets.Add
sht.Name = "PivotTab"
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")
'Set up the row & column fields
pvt.AddFields RowFields:=Array("Area", "Gp Br", "Ecars2Ticket-Reservation", "Car Status"), ColumnFields:="Office"
'Add item to the Report Filter
pvt.PivotFields("Area").Orientation = xlPageField
pvt.PivotFields("Gp Br").Orientation = xlPageField
pvt.PivotFields("Ecars2Ticket-Reservation").Orientation = xlPageField
pvt.PivotFields("Car Status").Orientation = xlPageField
pvt.PivotFields("Car Status").Orientation = xlColumnField
pvt.PivotFields("Area").Orientation = xlRowField
pvt.PivotFields("Gp Br").Orientation = xlRowField
pvt.AddDataField pvt.PivotFields("Ecars2Ticket-Reservation"), "Count of Ecars2Ticket-Reservation", xlSum
pvt.ManualUpdate = False
End Sub