SebastianHuang
New Member
- Joined
- Dec 5, 2019
- Messages
- 38
- Office Version
- 2013
- Platform
- Windows
Is there a way to make pivot tables with data and include certain headers and in columns and cells?
Like below. But I run into error when I try to turn it into a macro.
Sub PivotTable()
'
' PivotTable Macro
'
'
Range("B22").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"72000!R1C1:R229C29", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable25", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable25")
.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
With ActiveSheet.PivotTables("PivotTable25").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable25").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable25").PivotFields("TransDesc")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable25").PivotFields("PostingPeriod")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable25").AddDataField ActiveSheet.PivotTables( _
"PivotTable25").PivotFields("AMOUNT DR/(CR)"), "Sum of AMOUNT DR/(CR)", xlSum
Columns("B:H").Select
Selection.Style = "Comma"
End Sub
Like below. But I run into error when I try to turn it into a macro.
Sub PivotTable()
'
' PivotTable Macro
'
'
Range("B22").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"72000!R1C1:R229C29", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable25", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable25")
.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
With ActiveSheet.PivotTables("PivotTable25").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable25").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable25").PivotFields("TransDesc")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable25").PivotFields("PostingPeriod")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable25").AddDataField ActiveSheet.PivotTables( _
"PivotTable25").PivotFields("AMOUNT DR/(CR)"), "Sum of AMOUNT DR/(CR)", xlSum
Columns("B:H").Select
Selection.Style = "Comma"
End Sub