Create Pivot Table in VBA

A

Analyze_This

Board Regular
Joined
Oct 29, 2008
Messages
122
Looked all over the web and I can't find how to create a pivot table in excel. Lots of posts about it...none of them work. Here's the code that I have. I recorded it and just replaced the SourceData range with current region argument. Can anyone tell me why I keep getting a "Type Mismatch" error on this?

Code: 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        ActiveSheet.Range("A1").CurrentRegion, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable", DefaultVersion _
        :=xlPivotTableVersion12
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Been a while since I've done it, but pulled this code from one of my projects.

You need to define the data range in Test() and give two valid field names from the data table.
The TableName may also have to be unique, but saying that I ran the code twice and it created two pivot tables with the same name.

Code: 
Sub Test()

    CreatePivot Worksheets("Final Sample Data").Range("A1:K105"), "Team", "Area"

End Sub

Public Sub CreatePivot(rngSource As Range, sColumnHead1 As String, sColumnHead2 As String)

    Dim tmpWrkSht As Worksheet
    Dim sDestination As String
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    
    Set tmpWrkSht = ThisWorkbook.Worksheets.Add
    sDestination = tmpWrkSht.Name & "!R1C1"
    
    Set ptCache = ThisWorkbook.PivotCaches.Add( _
        SourceType:=xlDatabase, _
        SourceData:="'" & rngSource.Parent.Name & "'!" & rngSource.Address)
        
    Set pt = ptCache.CreatePivotTable( _
        TableDestination:=sDestination, _
        TableName:="TempPivotTable")
        
    With pt
        .PivotFields(sColumnHead1).Orientation = xlRowField
        .PivotFields(sColumnHead2).Orientation = xlDataField
        .ColumnGrand = False
    End With
    
    Application.CommandBars("PivotTable").Visible = False
        
End Sub
 
Last edited:
Upvote 0
I'm afraid that I'm not well enough versed in the Pivot Table code to modify that for my range of data. Thanks for the effort though. Looks like it just can't be done by defining a simple range.
 
Upvote 0
Try changing the TEST() procedure to:
Code: 
Sub Test()

    CreatePivot ActiveSheet.Range("A1").CurrentRegion, "Team", "Area"

End Sub

Change the word "Team" and "Area" to column headers from your data table.

The above example will create a unique list of "Team" and give a count of how many "Area" are in each and you need to have a cell selected within your data table.
 
Upvote 0
Got it! Turns out all you have to do is make it a named range (or format as a table) and then point SourceData to "=NamedRange"




Code: 
Sub SS_Trial()

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name _
        = "Raw"
    ActiveSheet.ListObjects("Raw").TableStyle = ""
    
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "=Raw", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion12
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
332
darmst24
D
R
  • Solved
Looping Through Workbook and making pivot tables on each worksheet
Replies
4
Views
330
rzacek87
R
S
  • Solved
Pivot Table Macro
Replies
4
Views
453
SCPbrito
S
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
200
Anthony47
A
S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
529
RoryA
RoryA

Forum statistics

Threads
1,225,169
Messages
6,183,318
Members
453,155
Latest member
joncaxddd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top