VBA Pivot Table Dynamic Range

[emad_mesalmy]

Hi Gays

i am beginner with VBA Code so please i ask your help to how to make below code of create pivot table with dynamic range:

Sub Pivot_Data()
'
' Pivot_Data Macro
'


'
Range("A1").Select


Selection.CurrentRegion.Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R34093C8", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R1C12", TableName:="PivotTable2", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(1, 12).Select
With ActiveSheet.PivotTables("PivotTable2")
.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("PivotTable2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Store Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Functional Amount"), "Sum of Functional Amount", _
xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("GL Date")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Number")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Number").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Number")
.PivotItems("2").Visible = False
.PivotItems("84").Visible = False
.PivotItems("85").Visible = False
.PivotItems("86").Visible = False
.PivotItems("91").Visible = False
.PivotItems("186").Visible = False
.PivotItems("1000").Visible = False
.PivotItems("1001").Visible = False
.PivotItems("1003").Visible = False
.PivotItems("1015").Visible = False
.PivotItems("1100").Visible = False
.PivotItems("1101").Visible = False
.PivotItems("1103").Visible = False
.PivotItems("1104").Visible = False
.PivotItems("1109").Visible = False
.PivotItems("1111").Visible = False
.PivotItems("1118").Visible = False
.PivotItems("1123").Visible = False
.PivotItems("1125").Visible = False
.PivotItems("1126").Visible = False
.PivotItems("1127").Visible = False
.PivotItems("1128").Visible = False
.PivotItems("1130").Visible = False
.PivotItems("1131").Visible = False
.PivotItems("1132").Visible = False
.PivotItems("1133").Visible = False
.PivotItems("1134").Visible = False
.PivotItems("1135").Visible = False
.PivotItems("10114").Visible = False
.PivotItems("84210").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Number"). _
EnableMultiplePageItems = True
End Sub

 

[Worf]

Hi

Sub Pivot_Data()
Dim lastcol%, dest As Range, lastrow%, pt As PivotTable
lastrow = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row
lastcol = Sheets("sheet1").Cells(lastrow, Columns.Count).End(xlToLeft).Column
' or use current region
Set dest = Sheets("sheet1").[L1]
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R" & lastrow & "C" & _
lastcol, Version:=6).CreatePivotTable TableDestination:=dest, TableName:="PivotTable2", DefaultVersion:=6
Set pt = Sheets("sheet1").PivotTables("PivotTable2")
With pt
    .ColumnGrand = True
    .ErrorString = ""
    .MergeLabels = False
    .NullString = ""
    .PageFieldOrder = 2
    .PageFieldWrapCount = 0
    .RowGrand = True:    .SaveData = True
    .PrintTitles = False
    .CompactRowIndent = 1
    .InGridDropZones = 0
    .DisplayFieldCaptions = True:    .ShowValuesRow = False
    .CalculatedMembersInFilters = False
    .RowAxisLayout xlCompactRow
End With
With pt.PivotCache
    .RefreshOnFileOpen = False
    .MissingItemsLimit = xlMissingItemsDefault
End With
pt.RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
With pt.PivotFields("Store Name")
    .Orientation = xlRowField
    .Position = 1
End With
pt.AddDataField pt.PivotFields("Functional Amount"), "Sum of Functional Amount", xlSum
With pt.PivotFields("GL Date")
    .Orientation = xlColumnField
    .Position = 1
End With
With pt.PivotFields("Number")
    .Orientation = xlPageField
    .Position = 1
End With
pt.PivotFields("Number").CurrentPage = "(All)"
With pt.PivotFields("Number")
    .PivotItems("2").Visible = False
    .PivotItems("84").Visible = False
    .PivotItems("85").Visible = False
    .PivotItems("10114").Visible = False
    .PivotItems("84210").Visible = False
End With
pt.PivotFields("Number").EnableMultiplePageItems = True
End Sub