This is a good one.

I am taking over a project from another analyst, who has moved out of state.
His coding was pretty cool to have two tables on a sheet. Lets call then
CurrentReport and LatestReport. The file will eventully be saved to
OldReport.xls (with a twist as I will explain). The template always current
data into the CurrentReport section and then looks at the link to the
OldReport for its LastReport section. The trick is that before the file is
saved the LatestReport query table is deleted and reassigned to the
CurrentReport range adddress. So when the new report is generated it looks
at the OldReport refering to the €œCurrent€� section thinking that is it is the
€œLatestReport€� section. Pretty cool.

Here is my problem: I am getting a 1004 error €˜Method range of object €˜
error message combined with another error.

He used code that rebuilds the datatable range: (error trapping statements
removed)
€¦Sheets("Corrective Action Record").QueryTables("LatestReport").Delete
Sheets("Corrective Action Record").QueryTables("Latest_Report").Delete€¦
then

€¦UpdateNameRangeforQT "Corrective Action Record", "CorrectiveActions"€¦

(3 out of 4 tabs querytable(1) ='LatestReport", on sheet("Corrective Action
Record" querytable(2) ='LatestReport" and I have a querytable(3)) This used
to work before I added the third suery table and had to rebuild the second
query table which seemed to put all objects in the wrong order.)

Public Sub UpdateNameRangeforQT(sSheetName As String, sRangeName As String)
Dim oSheet As Worksheet
Dim oQT As QueryTable
Dim oRange As Range
Dim oName As Name

'get the sheet
Set oSheet = ActiveWorkbook.Sheets(sSheetName)

'get the Query table
Set oQT = oSheet.QueryTables(1)

'get the range of cells occupied by the query table
Set oRange = oQT.ResultRange

'Get the workbook-level name object
Set oName = ActiveWorkbook.Names(sRangeName)

'Update what the name object refers to
oName.RefersTo = oRange

I found out that since I had to modify one of the tables it now resides in
position number 2 for that sheet and there is another querytable I use in
position Number 3. Yet when I try to reinstate those query tables it jams
and gives a does not exist message.
I tried :
If sSheetName = "Corrective Action Record" Then
If sRangeName = "Last_Date" Then
Set oQT = oSheet.QueryTables(3)
Else
Set oQT = oSheet.QueryTables(2)
End If
Else
Set oQT = oSheet.QueryTables(1)
End If


It still jams. Hmmm

How do I reorder the table position in the inding or make the code above work?
I could really use sonme help.