Excel 2010 gets error message on recorded macro

[MarshaP]

I recorded a macro in Excel 2010 to run a saved query and return the data. When I run the macro, I get a run-time error 5, invalid procedure call or argument. This is the code generated by the macro recorder:

Sub Macro5()
'
' Macro5 Macro
'
'
Workbooks.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Destination:=Range("$A$1")) _
.QueryTable
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_GL_Query_002"
.Refresh BackgroundQuery:=False
End With
End Sub


It ran OK when I was recording the macro, and runs OK if I run the query within Excel.

Thanks for any help.

 

[SkipVought]

hi,

Your macro ADDs a NEW querytable EVERY SINGLE TIME.

This clutters your sheet with unnecessary objects and it totally unnecessary.

All you need do, once a QT is added, is to REFRESH the QT. If you want to do this via code...

activesheet.listobjects(1).querytable.refresh false

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}