Sub CreateTable()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3").CurrentRegion, , xlYes).Name = "tbSource"
End Sub
Sub PivotTableCreator()
Dim PTname As String, sGood As String, sType As String, sQuantity As String, TableName As String
Dim lst As ListObject
With ActiveSheet
Set lst = .ListObjects(1)
TableName = lst.Name 'Name of Table
sGood = lst.HeaderRowRange.Cells(1, 1).Value 'Name of the good is found in first column of Table
sType = lst.HeaderRowRange.Cells(1, 2).Value 'Name of type of goods is found in second column of Table
sQuantity = lst.HeaderRowRange.Cells(1, 3).Value 'Quantity of goods is found in third column of Table
PTname = "PivotTable1" 'Name of PivotTable to be created
End With
Sheets.Add after:=ActiveWorkbook.Worksheets(ActiveSheet.Index)
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=TableName).CreatePivotTable _
TableDestination:=ActiveSheet.Name & "!R3C1", TableName:=PTname
With ActiveSheet.PivotTables(PTname)
With .PivotFields(sGood)
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields(sType)
.Orientation = xlRowField
.Position = 2
End With
.AddDataField .PivotFields(sQuantity), "Sum of " & sQuantity, xlSum
With .PivotFields(sGood)
.LayoutForm = xlTabular
.RepeatLabels = True
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
.ColumnGrand = False
End With
End Sub
Private Sub Worksheet_Activate()
Dim PT As PivotTable
For Each PT In Me.PivotTables
PT.RefreshTable
Next
End Sub
BradAre you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
You need to specify, however, either by name or number which columns are going to be used for the PivotTable. As written, the code assumes column numbers 1, 2 and 3 (the second index in .Cells in the three statements below. You will need to change those numbers to match your actual source data.
Open in new window
Alternatively, you could specify the exact text of the exact header labels that you want to use. Then you would use that text in those same statements like this:
Open in new window