Hot to Create a Pivot Table with VBA

[ChristopherBrandonKi]

Hello All,

I am new to using VBA to create Pivot tables and need it to automate a report where I take Data from several worksheets and create pivot tables on the first worksheet for every other worksheet in the workbook (but the worksheets will be deleted at the end of every week and repopulated with new worksheets in the same order with similar, but not the same, names). The format will be close to the same but the column headings may have to be adjusted so I think I will have to create each macro individually as opposed to creating a loop. I think I can do all the adjustments once I have a working Macro for the first one. I think I have compiled a mixture from other examples to do what I want it to do but I keep running into issues when it comes to actually creating the pivot table. I really can't seem to find any simple way to understand the format of VBA as it relates to Pivot Tables. Can anyone take a look at this code and tell me what syntax errors I seem to be making?


The error is saying "Variable not defined" when I get to the last section of this code.


__________________________________________________________________________________________________________________

Sub CreatingPivotTables()
'
' CreatingPivotTables Macro
'

'

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Dim PivotDest As Worksheet
Set WSD = Worksheets(3)
Set PivotDest = Worksheets("1Main ")

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
Worksheets(3).Activate
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)



Set PT = PTCache.CreatePivotTable(TableDestination:=PivotDest.Cells(30, 2), _
TableName:="PivotTable100")


PT.ManualUpdate = True


' Set up the row & column fields (This is where I am having the issues according to VBA errors. It's saying Variable Not Defined when it gets to "x1RowField")
With PT
.PivotFields("UPC #").Orientation = x1RowField
.PivotFields("Dollar on Hand").Orientation = x1DataField
.PivotFields("Quantity on Hand").Orientation = x1DataField
.PivotFields("Dollar Sold").Orientation = x1DataField
.DisplayFieldCaptions = False
End With


End Sub

_______________________________________________________________________________________________________________________________________________


Please Help!

[naveenmarapaka]

Hi.
would be better if you post data... show you desired result of pivot table..... Do it manually first and attach