Selecting Pivot Table Range with Dynamic Data

[Paul_Hossler]

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