Getting "Run-time error '1004': Select method of Range class failed"

Question

I have two (2) sheets. The first one has a button. The second sheet is my summary page, and I plan to have a button here that is linked to the button on my first sheet. However, I get the

Run-time error '1004': Select method of Range class failed.

I assigned Command1_Click() to be my button at Sheet2

Module1:

Sub Command1_Click()
Call Model01
End Sub

Module2:

Sub Model01()
Sheet1.Range("C8:GF8").Value = Sheet1.Range("C240:GF240").Value
Sheet1.Range("B7").Value = Sheet1.Range("B240").Value
r = DataUpdate()
End Sub

Module3:

Function DataUpdate()
If (validateVer() = False) Then
    Exit Function
End If

Dim AsCn, AsRs, SQL
Dim x, y, WDATE, NGQTY, FAQTY, RWQTY, RNQTY, TotalCell, TotalCellx
Dim FA_TBL As Variant   ' (y, x)
Dim RW_TBL As Variant   ' (y, x)
Dim RN_TBL As Variant   ' (y, x)
Dim NG_TBL As Variant   ' (y, x)


'Sheet => Table
Sheet1.Range("C2:GS2").Select
Selection.ClearContents
FA_TBL = Sheet1.Range("A2:GS2")
Sheet1.Range("C4:GS4").Select
Selection.ClearContents
RW_TBL = Sheet1.Range("A4:GS4")
Sheet1.Range("C5:GS5").Select
Selection.ClearContents
RN_TBL = Sheet1.Range("A5:GS5")
Sheet1.Range("A9:GS208").Select
Selection.ClearContents
Sheet1.Range("C9").Select
NG_TBL = Sheet1.Range("A8:GS208")

'Connect DataBase ODBC
Set AsCn = CreateObject("ADODB.Connection")
AsCn.Open "Provider=IBMDA400;Data Source=FCP2040;", "FCPMIS", "AUTOSENT"
'AsCn.Open "dsn=FCP2040;uid=FCPMIS;pwd=AUTOSENT"

'Get Total cell
TotalCell = 3
While NG_TBL(1, TotalCell) <> "Total"
    TotalCell = TotalCell + 1
Wend

..

'Get NG Qty
y = 2
NGQTY = 0
While Not (AsRs.EOF)

    NG_TBL(y, 1) = AsRs(1)
    NG_TBL(y, 2) = AsRs(2)
    NGQTY = 0
    x = 3
    While x < TotalCell
        WDATE = Right(Year(NG_TBL(1, x)), 2) & Right("00" & Month(NG_TBL(1, x)), 2) & Right("00" & Day(NG_TBL(1, x)), 2)
        If Not (AsRs.EOF) Then
            If ((NG_TBL(y, 1) = AsRs(1)) And _
                ((WDATE = AsRs(0)) Or ((x + 1) = TotalCell))) Then
                NG_TBL(y, x) = AsRs(3)
                NGQTY = NGQTY + AsRs(3)
                AsRs.MoveNext
            Else
                If FA_TBL(1, x) <> "" Then
                    NG_TBL(y, x) = 0
                End If
            End If
        Else
            If FA_TBL(1, x) <> "" Then
                NG_TBL(y, x) = 0
            End If
        End If
        x = x + 1
    Wend
    NG_TBL(y, TotalCell) = NGQTY
    y = y + 1
Wend

'Sheet => Table
Sheet1.Range("A2:GS2") = FA_TBL
Sheet1.Range("A4:GS4") = RW_TBL
Sheet1.Range("A5:GS5") = RN_TBL
Sheet1.Range("A8:GS208") = NG_TBL


'--------------------------------------------------------------------------------
'   Sort
'--------------------------------------------------------------------------------
If (TotalCell \ 26) = 0 Then
    TotalCellx = Chr(TotalCell + 64)
Else
    TotalCellx = Chr(TotalCell \ 26 + 64) & Chr(TotalCell Mod 26 + 64)
End If

Sheet1.Range("A8:" & TotalCellx & "100").Select
Selection.Sort Key1:=Range(TotalCellx & "9"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    SortMethod:=xlPinYin
Sheet1.Range("C9").Select



'Close Recordset
AsRs.Close
Set AsRs = Nothing

'Close DataBase ODBC
AsCn.Close
Set AsCn = Nothing
End Function

Answer 1

You can only select a range on an active sheet. Hence you can't directly select a range in Sheet1 from a button in Sheet2. The solution is not to activate the sheet before selecting. The solution is to not use Select at all. That method is almost never needed. For example

Sheet1.Range("C2:GS2").Select
Selection.ClearContents

is more cleanly expressed as

Sheet1.Range("C2:GS2").ClearContents

See How to avoid using Select in Excel VBA for other suggestions for eliminating needless selects in your code.