VBA: Create Pivot Table with Dynamic Range & Sheet Name

[donatepresent]

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

 

[Domenic]

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...

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]

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...

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]

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...

    '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]

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

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

 

[donatepresent]

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

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!!!

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!