Code that only works when it is run from a certain tab

[gheyman]

I have code tied to a userform (on the form I have a run button which runs the code). If I launch the Userform from one tab, the code runs great. If I launch the Userform (via a button) from another tab it errors out. (run-time error'1004': Select method of range class failed)

It errors on "Sheet2.Range("C5").Select"

Why does it run from one tab and not another

Sub Get_IMD()

'Get Data from IMD_BoM Item Master Detail tab and place it in CBOM tab

Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

Dim IMDLstRw As Long

    IMDLstRw = Sheet5.Range("A" & Rows.Count).End(xlUp).Row

'End Part ID
    Sheet5.Range("A9:A" & IMDLstRw).Copy
        Sheet2.Range("C5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                
'BOM Indented Level
    Sheet5.Range("D9:D" & IMDLstRw).Copy
        Sheet2.Range("E5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                 
'Make/Buy
    Sheet5.Range("R9:R" & IMDLstRw).Copy
        Sheet2.Range("F5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
 
'Part ID
    Sheet5.Range("G9:G" & IMDLstRw).Copy
        Sheet2.Range("H5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

'Description
    Sheet5.Range("I9:I" & IMDLstRw).Copy
        Sheet2.Range("I5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

'Revision ID
    Sheet5.Range("H9:H" & IMDLstRw).Copy
        Sheet2.Range("J5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

'U/M
    Sheet5.Range("J9:J" & IMDLstRw).Copy
        Sheet2.Range("K5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

'Commodity & Commodity Cd Desc
    Sheet5.Range("K9:L" & IMDLstRw).Copy
        Sheet2.Range("L5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
 
'Part Type Desc
    Sheet5.Range("Q9:Q" & IMDLstRw).Copy
        Sheet2.Range("N5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

'Floor Stock (Y/N)
    Sheet5.Range("AJ9:AJ" & IMDLstRw).Copy
        Sheet2.Range("O5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
 
'Component Qty
    Sheet5.Range("F9:F" & IMDLstRw).Copy
        Sheet2.Range("P5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
 
'Last Supplier & Last Receipt Date
    Sheet5.Range("U9:V" & IMDLstRw).Copy
        Sheet2.Range("X5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                 
 Calculate
                 
'Reformat Level
'Step is Required to calculate the SA Qty
Sheet2.Select
Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
Range("A3").Select

End Sub

 

[RoryA]

You can't select a range on a sheet unless that sheet is active. But you don't need to select anything there - instead of this:

 Sheet2.Range("C5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

just use:

 Sheet2.Range("C5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ 
    SkipBlanks:=False, Transpose:=False