Dynamic Pivot Table Range

[ai1094]

Hello, I was wondering if there was a basic VBA script that tells the code to select ALL my data in a sheet and create a pivot table into a new sheet. I've tried to look online but had some trouble trying to fix their code to work for my purpose. Few things to keep in mind:

- I want the the macro to be able to construct the pivot regardless of the sheet name
- To select all rows+columns because data may change (format stays the same)

Does anyone know how I would be able to go about this?

Here is a sample code I found online:

Sub TT()


Dim shtSrc As Worksheet, shtDest As Worksheet
Dim pc As PivotCache


Set shtSrc = ActiveSheet


Set shtDest = shtSrc.Parent.Sheets.Add()


Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=shtSrc.Range("A1").CurrentRegion)
pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
TableName:="PivotTable1"


With shtDest.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With


End Sub

 

[Worf]

An example:

Sub TT()
Dim shtSrc As Worksheet, sd As Worksheet, pc As PivotCache, pt As PivotTable
Set shtSrc = ActiveSheet
Set sd = shtSrc.Parent.Sheets.Add()
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=shtSrc.[A1].CurrentRegion)
pc.CreatePivotTable TableDestination:=sd.[A3], TableName:="PivotTable1"
Set pt = sd.PivotTables("pivottable1")
With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With
sd.Activate
With pt.PivotFields("Month")
    .Orientation = xlPageField
    .Position = 1
End With
With pt.PivotFields("Sales Person")
    .Orientation = xlRowField
    .Position = 1
End With
pt.AddDataField pt.PivotFields("Amount"), "Sum of Amount", xlSum
End Sub