how to use dynamic source data in a recorded macro vba when generating a pivot table?

Typically you’d do something like this to create a pivottable from the data in the active sheet

    Dim wb As Workbook, wsSource As Worksheet, wsDest As Worksheet
    Dim pc As PivotCache, pt As PivotTable
    
    Set wb = ActiveWorkbook
    Set wsSource = ActiveSheet
    Set wsDest = wb.Sheets.Add(after:=wb.Sheets(wb.Sheets.Count))
    wsDest.Name = "Pivot"
    
    'create the pivot cache first
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
               SourceData:=wsSource.Range("A1").CurrentRegion, Version:=6)

    '...then the pivottable
    Set pt = pc.CreatePivotTable(TableDestination:=wsDest.Range("A3"), _
                                 TableName:="PivotTable1", DefaultVersion:=6)