Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
- 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
- PivotCaches.Create
Add Comment
Please, Sign In to add comment