an extra parenthesis crept in: 
Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & Sheet3.Name & "'!" & mySourceData).CreatePivotTable(TableDestination:="'" & Sheet3.Name & "'!" & myDestinationRange, TableName:="PivotTableExistingSheet")