Creating a pivot table using a macro

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'Prior To Pivot Table'!R2C1:R1194C3", TableDestination:="", TableName:= _
        "PivotTable2"
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Proj_only" _
        , "Data")
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("GL WIP")
        .Orientation = xlDataField
        .Name = "Sum of GL WIP"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Con Reg WIP")
        .Orientation = xlDataField
        .Name = "Sum of Con Reg WIP"
        .Function = xlSum
    End With
End Sub

Sub MakeTable()
'declare the row, column, page and data field variables
Dim Pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim pageField2 As String
Dim pageField3 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String
'delete pivot sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'
Set s = Sheets("Data")
With Worksheets.Add
    .Name = "Pivot Sheet"
End With
    
    'Pass variable names to a String variable
    pageField1 = s.Cells(1, 2).Value  'page variable "Dispo code"
    rowField1 = s.Cells(1, 3).Value  'row field "Op No"
      
    colField1 = s.Cells(1, 10).Value  'colums field "Defect"
    dataField = s.Cells(1, 11).Value  'data to summarize "Tons"
'Name the list range
Worksheets("Data").Activate
ActiveSheet.Range("a1").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Items"
'create pivot cache
Set PtCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:=s.Range("Items")) 'entire contents of sheet
    
'create pivot table from cache
Set Pt = PtCache.CreatePivotTable( _
    TableDestination:=Sheets("Pivot Sheet").Range("A3"), _
    TableName:="QualCodeTable")
    
'add fields
With Pt
    .PivotFields(rowField1).Orientation = xlRowField   'Op No
    .PivotFields(pageField1).Orientation = xlPageField  'Dispo code
    .PivotFields(colField1).Orientation = xlColumnField  'Defect
    .PivotFields(dataField).Orientation = xlDataField  'sum of Tons
End With
Worksheets("Pivot Sheet").Columns("A:DD").AutoFit
Application.ScreenUpdating = True
'create pivot chart
'first delete chart if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot Chart").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'
    
    'add chart
    Sheets("Pivot Sheet").Activate
    Sheets("Pivot Sheet").Range("B6").Select  'selects a cell in the pivot table
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Pivot Sheet").Range("E6")  'selects a cell on the pivot sheet as the range
    ActiveChart.Location xlLocationAsNewSheet, "Pivot Chart"  'creates a new sheet named "Pivot Chart"
    'ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlColumnStacked
    
    
End Sub