VBA - Insert pivot table into existing sheet on next available row

[ravecake]

Hi,

I have a workbook where the first sheet ("main") is a sheet with pivot tables based on the data in each other sheet in the workbook. These other sheets are data sheets (by date) extracted from an external database. I add them to this workbook every day as follows: [ main ] [ 20230110 ] [ 20230109 ] [ 20230106 ] etc.

I need a macro that will create a pivot table based on the available data in the second sheet (which represents the current day's data), and then insert that table in the first sheet "main" on the next available empty row in Column A. Because I will be doing this repeatedly with new data I need the macro to dynamically give each pivot table a unique name to avoid duplicates.

I have the following code but keep running into 'An object is required' errors. Any help would be greatly appreciated!

Sub CreateDynamicPivotTable()

    Dim mainSheet As Worksheet
    Set mainSheet = ActiveWorkbook.Sheets("main")
    Dim dataSheet As Worksheet
    Set dataSheet = ActiveWorkbook.Sheets(2)

    Dim lastRow As Long
    lastRow = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row

    Dim ptDestination As Range
    Set ptDestination = mainSheet.Range("A" & lastRow + 1)

    Dim pivotCache As PivotCache
    Set pivotCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataSheet.Range("A1").CurrentRegion)

    Dim pivotTableName As String
    pivotTableName = "PivotTable_" & Format(Now, "yyyymmdd_hhmmss")

    Dim pivotTable As PivotTable
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=ptDestination, _
        TableName:=pivotTableName)

End Sub