Excel VBA Macro for Pivot Table with Dynamic Data Range

Posted by John Ziebro on Stack Overflow See other posts from Stack Overflow or by John Ziebro
Published on 2012-09-25T15:43:54Z Indexed on 2012/09/26 15:37 UTC
Read the original article Hit count: 306

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about osx

Related posts about excel