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