Excel VBA Macro for Pivot Table with Dynamic Data Range

Question

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

You say this is being performed on a CSV file? What are the chances you don't have a sheet named "PivotTable"? — scott
– scott, Commented Sep 25, 2012 at 16:26
I thought that Dim WSD as Worksheet, then Set WSD = Worksheets("PivotTable") dynamically created the Worksheet named PivotTable. Is this not the case? — Liquidgenius
– Liquidgenius, Commented Sep 25, 2012 at 16:56
Renaming the worksheet from "Sheet1" to "PivotTable" manually and then running the macro, bypasses the orginal error, so your insight helped. Now I am erroring out in the section commented as ' Create the Pivot Table from the Pivot Cache. The error is "Method 'CreatePivotTable' of object 'PivotCache' failed" — Liquidgenius
– Liquidgenius, Commented Sep 25, 2012 at 17:02
I added ActiveSheet.Name = "PivotTable" after instantiating the variables near the top. — Liquidgenius
– Liquidgenius, Commented Sep 25, 2012 at 17:09

scott · Accepted Answer · 2012-09-28 13:20:48Z

2

They changed the object model for PivotCaches. The method you need in 2007-2010 (that use VBA version 7 instead of version 6) is

 PivotCaches.Create

answered Sep 28, 2012 at 13:20

scott

2,2951 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pedram · Accepted Answer · 2012-09-25 15:55:38Z

1

Any reason you are using VBA other than the changing number of rows?

If you are using Excel 2007 / 2010, create a regular table /list (Ctrl-L) from your original data. You can give it a name as well. Then create a Pivot Table and use the table name as the data source. As you add rows your table will expand and you can then just refresh the Pivot Table (F5, or using VBA).

If you are in Excel 2003, you can create dynamic named ranges as well. It's slightly more complex (and a lot more ugly) but I can walk you through it if you are stuck on an older version.

answered Sep 25, 2012 at 15:55

pedram

3,1174 gold badges30 silver badges44 bronze badges

6 Comments

Liquidgenius Over a year ago

I am using VBA, because this is an automated process that runs on a cvs file I receive daily via email. I run the macro off of a script that is triggered by email rules.I am using Excel 2011 for Mac. It has VBA 14.0 in it.

Liquidgenius Over a year ago

If I can get this macro to work, I can fully automate the process of collecting the data and will have daily updated info that is used as a basis for visual analytics in Tableau.

pedram Over a year ago

Obvious one, but is the worksheet called exactly PivotTable? Does Sheets("PivotTable").Activate bring up the sheet?

Liquidgenius Over a year ago

Short answer, No. The macro fails when I set WSD to the name "PivotTable".

Liquidgenius Over a year ago

My impression of this code was that it was supposed to create the worksheet "PivotTable" dynamically, but was failing for some reason.

|

Collectives™ on Stack Overflow

Excel VBA Macro for Pivot Table with Dynamic Data Range

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related