Hi Grant,
Thanks to all who have responded. I've pretty much got the answer I needed
and scripted my code accordingly. There's only one problem....... After
running the code repeatedly, clicking on the Insert - Name - Define Menu
reveals defined names "Server1_Detail_1", "Server1_Detail_2",
"Server1_Detail_3" etc etc all with the similar invalid references ie
=Sheet2!#REF!.
LIke this:
Sub DeleteAllQueries()
Dim qt As QueryTable
Dim WSh As Worksheet
Dim strQueryName
Dim oName as Name
For Each WSh In ThisWorkbook.Worksheets
strQueryName = WSh.Range("D2")
For Each qt In WSh.QueryTables
If InStr(qt.Name, strQueryName) Then
qt.ResultRange.ClearContents
qt.ResultRange.Delete
qt.Delete
End If
for each oName in Activeworkbook.Names
If Instr(oName.Name,strQueryname) Then
oName.Delete
End If
Next oName
Next qt
Next WSh
End Sub
Regards,
Jan Karel Pieterse
Excel MVP
www.jkp-ads.com