I have recorded a macro to first delete all cells in a sheet and then create Pivot table from Union Query .
After recording , when i run it, it gives me error.

Following is the macro

Can anyone correct it, and also change its path to active workbook

Sub PivotQuery_Macro()
'
' Macro13 Macro
'

Cells.Delete Shift:=xlUp
Cells.Delete Shift:=xlUp
Cells.Delete Shift:=xlUp
Range("A1").Select
Workbooks("Automated LD Record R-2.xls").Connections.Add2 _
"Query from Excel Files1", "", Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\USERS\ZUBAI_000\Desktop\Automated LD Record R-2.xls;DefaultDir=C:\USERS\ZUBAI_000\Desktop;DriverId=790;M" _
), Array("axBufferSize=2048;PageTimeout=5;")), _
"SELECT `FO$`.Outage__Type, `FO$`.Month, `FO$`.`Wt#Add#Cap#__MWh`, `FO$`.`Wt#Cap#__MWh`, `FO$`.`Wt#Total#__MWh`" & Chr(13) & "" & Chr(10) & "FROM `C:\USERS\ZUBAI_000\Desktop\Automated LD Record R-2`.`FO$` `FO$`" & Chr(13) & "" & Chr(10) & "UNION ALL" & Chr(13) & "" & Chr(10) & "SELECT `PFO R1$`.Outage__Type, `PFO R1$`.Month, `PFO R1$`.`Wt#Add#Cap#__MWh`, `PFO R1$`.`Wt#Cap#__MWh`, `PFO R1$`.`Wt#Total#__MWh`" & Chr(13) & "" & Chr(10) & "FROM `C:\USERS\ZUBAI_000\Desktop\Automated" & _
"rd R-2`.`PFO R1$` `PFO R1$`" & Chr(13) & "" & Chr(10) & "", 2
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:= _
xlPivotTableVersion10)
.Connection = Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\USERS\ZUBAI_000\Desktop\Automated LD Record R-2.xls;DefaultDir=C:\USERS\ZUBAI_000\Desktop;DriverId=790;M" _
), Array("axBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = _
"SELECT `FO$`.Outage__Type, `FO$`.Month, `FO$`.`Wt#Add#Cap#__MWh`, `FO$`.`Wt#Cap#__MWh`, `FO$`.`Wt#Total#__MWh`" & Chr(13) & "" & Chr(10) & "FROM `C:\USERS\ZUBAI_000\Desktop\Automated LD Record R-2`.`FO$` `FO$`" & Chr(13) & "" & Chr(10) & "UNION ALL" & Chr(13) & "" & Chr(10) & "SELECT `PFO R1$`.Outage__Type, `PFO R1$`.Month, `PFO R1$`.`Wt#Add#Cap#__MWh`, `PFO R1$`.`Wt#Cap#__MWh`, `PFO R1$`.`Wt#Total#__MWh`" & Chr(13) & "" & Chr(10) & "FROM `C:\USERS\ZUBAI_000\Desktop\Automated" & _
"rd R-2`.`PFO R1$` `PFO R1$`" & Chr(13) & "" & Chr(10) & ""
.Cr
Table TableDestination:="Summary!R1C1", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
End With
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Outage__Type")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Month")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Outage__Type")
.PivotItems("Totals").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Month")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Wt#Total#__MWh"), "Count of Wt#Total#__MWh", _
xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of Wt#Total#__MWh")
.Caption = "Wt. Total MWh"
.Function = xlSum
.NumberFormat = "#,##0.000"
End With
End Sub