Excel VBA Macro for Pivot Table with Dynamic Data Range
- by John Ziebro
CODE IS WORKING! THANKS FOR THE HELP!
I am attempting to create a dynamic pivot table that will work on data that varies in the number of rows. Currently, I have 28,300 rows, but this may change daily.
Example of data format as follows:
Case Number Branch Driver
1342 NYC Bob
4532 PHL Jim
7391 CIN John
8251 SAN John
7211 SAN Mary
9121 CLE John
7424 CIN John
Example of finished table:
Driver NYC PHL CIN SAN CLE
Bob 1 0 0 0 0
Jim 0 1 0 0 0
John 0 0 2 1 1
Mary 0 0 0 1 0
Code as follows:
Sub CreateSummaryReportUsingPivot()
' Use a Pivot Table to create a static summary report
' with model going down the rows and regions across
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("PivotTable")
'Name active worksheet as "PivotTable"
ActiveSheet.Name = "PivotTable"
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Driver", ColumnFields:="Branch"
' Set up the data fields
With PT.PivotFields("Case Number")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
With PT
.ColumnGrand = False
.RowGrand = False
.NullString = "0"
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
