PDA

View Full Version : Selecting Pivot Table Range with Dynamic Data



Simpleton
02-21-2020, 09:15 PM
Hi y'all. Completely new to the world of VBA, so I apologize for my utter ignorance. Three hours of googling hasn't brought me any closer to a solution, so I'm asking here. I'm trying to make a pivot table for dynamic data ranges. Found the below code which works great if you tell it the range, but can't figure out how to select a range from A1:last cell used instead of R100. Tried all sorts of things with SpecialCells(xlCellTypeLastCell) and a number of other suggestions online, but I can't for the life of me figure out how to work any of them into the code. At this point I wouldn't even mind a solution with no explanation, but the latter would be appreciated as well. Thanks!

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String


'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)


'Create a new worksheet
Set sht = Sheets.Add


'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)


'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")


End Sub

Paul_Hossler
02-22-2020, 06:36 AM
Some simplifications




Option Explicit


Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As Range
Dim SrcData As Range


'Determine the data range you want to pivot
Set SrcData = ActiveSheet.Cells(1, 1).CurrentRegion


'Create a new worksheet
Set sht = Sheets.Add


'Where do you want Pivot Table to start?
Set StartPvt = sht.Range("A3")


'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)


'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")




End Sub