Populating a Pivot Table with VBA

[PipBoy808]

I use the code below to generate a pivot table:

'Create the Cache
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=DataRange)
PivotSheet.Select
'Create the Pivot table
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
TableDestination:=Range("A6"), TableName:="PGPivot")
ActiveWorkbook.ShowPivotTableFieldList = True
'Adding fields
??????????????????

However, I'm not sure how to add fields. I found the following code, but it keeps returning errors:

With .PivotFields("Stock Id") 'returns "Object variable or With block variable not set" error
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Total Stock")
.Orientation = xlDataField
.Position = 1
.Caption = "Total Stock"
.Function = xlSum
End With

Can anyone help me out? I'm looking to add the field 'Stock Id' to rows and sum of 'Total Stock' to the values part of the field list.

Any help would be much appreciated. I'd be lost without this forum! Thanks

 

[rumshar]

I use the code below to generate a pivot table:

'Create the Cache
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=DataRange)
PivotSheet.Select
'Create the Pivot table
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
TableDestination:=Range("A6"), TableName:="PGPivot")
ActiveWorkbook.ShowPivotTableFieldList = True
'Adding fields
??????????????????

However, I'm not sure how to add fields. I found the following code, but it keeps returning errors:

With .PivotFields("Stock Id") 'returns "Object variable or With block variable not set" error
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Total Stock")
.Orientation = xlDataField
.Position = 1
.Caption = "Total Stock"
.Function = xlSum
End With

Can anyone help me out? I'm looking to add the field 'Stock Id' to rows and sum of 'Total Stock' to the values part of the field list.



Any help would be much appreciated. I'd be lost without this forum! Thanks

Hi Friend,
The best way to learn pivot Table automation is to record macro while you create PT. This is how I learnt (not 100% yet)
Looking at your code(to add fields), try adding PT just before .pivotfields like:

With PT.PivotFields("Stock Id") 'this should not throw any error
.Orientation = xlRowField
.Position = 1
End With

With PT.PivotFields("Total Stock")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.Caption = "Total Stock " ' Space Added to avoid error
End With

You should have uploaded your file for better result.


With Regards
Rudra

 

[PipBoy808]

It's true, I realised that the best way is to just record myself doing it. Thanks for the advice!