I am using a spreadsheet. It has two tabs MAIN and TOTAL CASH. On the MAIN tab is a BUTTON and a cell where to enter a number. After you put a number in the cell and click the button, the results of the query is in the tab TOTAL CASH


Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("TOTAL CASH")
Numb = Range("H6")


If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("T_CASH")
Query.Sql = "SELECT CUST_TBLE.NAME, CASH_TBLE.SAV_AMT " _
& "FROM JES.CUST TBLE CUST TBLE,
JES.CASH_TBLE CASH_TBLE" _
& "WHERE CUST_TBLE.NUM_ID = CASH_TBLE.NUM_ID AND CUST_TBLE.NUM_CODE IN (" & NumList & ") "




Query.Refresh (False)


End Sub

This works.

In a new workbook, I created two tabs, MAIN and MONEY. On the MAIN tab is a BUTTON and a cell where to enter a number. After you put a number in the cell and click the button, I want the results of the query in the tab TOTAL CASH. This is not working. This code is very simiar to the code above that works. The only difference is the query. I am getting this error: run-time error '9'-- Subscript out of range. This is the line with the error:
Set Query = QuerySheet.QueryTables("MNY")

Why does one code work, and the other code does not?

This is the code that does not work.

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("MONEY")
Numb = Range("H6")


If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("MNY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, extract(month from NUMB_DATES_TBLE.DAY_MTH_YR) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name,
extract(month from NUMB_DATES_TBLE.DAY_MTH_YR)




Query.Refresh (False)


End Sub