Dynamic VBA Macro to Create Pivot Table

[Bkisley]

I have tried everything I know so far with no success.

Trying to do
I have a macro that creates a new tab and converts a data set into a formatted table named "Data Table"
The next macro (until I get this one working which I will then combine with my first macro) needs to create a new tab called "Pivot Table" and create a Pivot Table named "Pivot Table Final" of the formatted table named "Data Table" in cell A1. You will see the range I have is A1:R1000 this is just to be sure everything is captured.

This is where I keep getting stuck. It shows "invalid procedure call or argument". I have tried changed the table name directly in the code below and removing the last part of the code shown here, but still nothing.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Date Table!R1C1:R1000C18", Version:=6).CreatePivotTable TableDestination:= _
"Pivot Table!R1C1", TableName:="PivotTable3", DefaultVersion:=6
ActiveSheet.PivotTables("PivotTable3").Name = "PivotTableFinal"

PLEASE HELP!

 

[LazyBug]

Try this (didn't check):

Dim PTCache As PivotCache, PTable As PivotTable, psh As Worksheet

Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("YourDataSheetName").Range("A1").CurrentRegion)
  
ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
Set psh = Worksheets(1):    psh.Name = "PivotTable"
  
Set PTable = ActiveSheet.PivotTables.Add(PTCache, Range("A1"))
PTable.Name = "PivotTableFinal"