Excel VBA - Generalize Pivot Source Data / Range

Question

I recorded the pivot table macro and I'm trying to generalize source data instead of going off of sheet name "REPORTS"

It grabs all the data from active sheet despite what the name of the sheet.

This way I can use the macro to create a pivot table for any active sheet:-

Sheets("**REPORTS**").Select
Range("A1").Select
Sheets.Add.Name = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Sheets("**REPORTS**").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion15
Sheets("Pivot").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

Answer 1

Sub TT()

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

    Set shtSrc = ActiveSheet

    Set shtDest = shtSrc.Parent.Sheets.Add()
    shtDest.Name = shtSrc.Name & "-Pivot"

    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

Answer 2

This will not add any data to the pivot table but it will create it

Sub Example()
    Dim PrevSheet As Worksheet
    Set PrevSheet = ActiveSheet
    Range("A1").Select
    Sheets.Add.Name = "Pivot"
    PrevSheet.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=ActiveSheet.UsedRange, _
                                      Version:=xlPivotTableVersion15).CreatePivotTable _
                                      TableDestination:="Pivot!R3C1", _
                                      TableName:="PivotTable1", _
                                      DefaultVersion:=xlPivotTableVersion15
    Sheets("Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
End Sub