VBA: Create Pivot Table with Dynamic Range & Sheet Name

donatepresent

New Member
Joined
Oct 4, 2017
Messages
21
Hi,

I would like to run a macro that inserts a pivot table on any data table and with any sheet name that has varying rows and columns.

I was able to find the below code through Googling, which works in capturing all the data in all rows and columns for the pivot table.

Where I am stuck is how do I make the bolded part of this code not restricted to "Sheet1!R1C1:R" but rather have it work no matter what the current active sheet name is?

If there is a simpler and shorter code, please advise, thanks very much! :)

Sub Pivot_Table()

Dim lastRow As Long
Dim lastCol As Long

lastRow = 1
lastCol = 1

While ActiveSheet.Cells(lastRow, lastCol).Value <> ""
lastCol = lastCol + 1
Wend

lastCol = lastCol - 1

While ActiveSheet.Cells(lastRow, 1).Value <> ""
lastRow = lastRow + 1
Wend

lastRow = lastRow - 1

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & lastRow & "C" & lastCol, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion12


ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,494
You can simply omit the sheet, (ie. "R1C1:R"). However, here's another way. The following macro uses two separate steps in creating the pivot table. First it creates the pivot cache, and then it creates the pivot table based on the newly created pivot cache...

VBA Code:
Sub Create_PivotTable()

    'get the source data
    Dim source_data As Range
    Set source_data = ActiveSheet.Range("A1").CurrentRegion
  
    'create the pivot cache
    Dim pivot_cache As PivotCache
    Set pivot_cache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        sourceData:=source_data)
  
    'add a new worksheet
    Worksheets.Add
  
    'create the pivot table from the pivot cache
    Dim pivot_table As PivotTable
    Set pivot_table = ActiveSheet.PivotTables.Add( _
        PivotCache:=pivot_cache, _
        TableDestination:=Range("A1"))
  
End Sub

Hope this helps!
 

donatepresent

New Member
Joined
Oct 4, 2017
Messages
21
You can simply omit the sheet, (ie. "R1C1:R"). However, here's another way. The following macro uses two separate steps in creating the pivot table. First it creates the pivot cache, and then it creates the pivot table based on the newly created pivot cache...

VBA Code:
Sub Create_PivotTable()

    'get the source data
    Dim source_data As Range
    Set source_data = ActiveSheet.Range("A1").CurrentRegion
 
    'create the pivot cache
    Dim pivot_cache As PivotCache
[B]    Set pivot_cache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        sourceData:=source_data)[/B]
 
    'add a new worksheet
    Worksheets.Add
 
    'create the pivot table from the pivot cache
    Dim pivot_table As PivotTable
    Set pivot_table = ActiveSheet.PivotTables.Add( _
        PivotCache:=pivot_cache, _
        TableDestination:=Range("A1"))
 
End Sub

Hope this helps!
Hi Domenic,

I'm getting a run-time error '13': Type mismatch as per above bolded lines.

Sorry, I'm not an VBA coder if this is a simple fix.

Thanks
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,494
When I tested my code, it created the pivot table successfully. I didn't encounter any errors. But it looks like we'll need to assign the range for the source data as a string instead of a Range object. So try the following instead...

VBA Code:
    'create the pivot cache
    Dim pivot_cache As PivotCache
    Set pivot_cache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        sourceData:=source_data.Address(external:=True))

Does this help?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,494

ADVERTISEMENT

Actually, I see that you also wanted to name your pivot table. So try the following instead...

VBA Code:
Sub Create_PivotTable()

    'create the pivot cache
    Dim pivot_cache As PivotCache
    Set pivot_cache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        sourceData:=Range("A1").CurrentRegion.Address)
    
    'add a new worksheet
    Worksheets.Add
    
    'create the pivot table from the pivot cache
    Dim pivot_table As PivotTable
    Set pivot_table = ActiveSheet.PivotTables.Add( _
        PivotCache:=pivot_cache, _
        TableDestination:=Range("A1"), _
        TableName:="PivotTable1")
    
End Sub
 
Solution

donatepresent

New Member
Joined
Oct 4, 2017
Messages
21
Actually, I see that you also wanted to name your pivot table. So try the following instead...

VBA Code:
Sub Create_PivotTable()

    'create the pivot cache
    Dim pivot_cache As PivotCache
    Set pivot_cache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        sourceData:=Range("A1").CurrentRegion.Address)
   
    'add a new worksheet
    Worksheets.Add
   
    'create the pivot table from the pivot cache
    Dim pivot_table As PivotTable
    Set pivot_table = ActiveSheet.PivotTables.Add( _
        PivotCache:=pivot_cache, _
        TableDestination:=Range("A1"), _
        TableName:="PivotTable1")
   
End Sub

OMG! YES, THANK YOU VERY MUCH!!! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,447
Messages
5,636,328
Members
416,914
Latest member
DWC199

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
Top