I can't see the changes in the video reflected in the code you sent me, so I am not sure what was not working for you ?
In the code you have sent me, I don't know what the first 4 lines are supposed to do so I have commented it out.
Also what you have recorded only sets up the "empty" pivot table.
The macro below is mostly following what was in the Video you referred to.
I have made a few changes.
The sheet you want that has the data in it to use as the basis of the pivot has to be the active sheet when you run the code.
The code also currently assumes your data range starts in A1 and has at least 3 columns of data in it.
VBA Code:
Sub RecordedMacroModified()
Dim lr As Long
Dim lc As Long
Dim dataWSName As String
Dim pivotWSName As String
Dim pivotWS As Worksheet
Dim dataRng As Range
dataWSName = ActiveSheet.Name
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
If lr < 2 Or lc < 3 Then
MsgBox "Must have data for a Pivot Table"
Exit Sub
End If
With Worksheets(dataWSName)
Set dataRng = .Range(.Cells(1, 1), .Cells(lr, lc))
End With
' Additional undocumented steps recorded by user
'Range("A1:AE2").Select
'Range("AE2").Activate
'Selection.Delete Shift:=xlUp
'Range("A1:AE16").Select
Sheets.Add
Set pivotWS = ActiveSheet
pivotWSName = pivotWS.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataRng, Version:=6).CreatePivotTable _
TableDestination:=pivotWS.Range("A3"), TableName:="PivotTable1", DefaultVersion _
:=6
Sheets(pivotWSName).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
End Sub
