Error '1004: Select method of Range class failed

[gheyman]

If I am on the tab "CMCS Tracker" this code runs perfectly. But if I try to run it while on another tab I get the run time error.

I wanted to launch the code from a button on another tab named "Search"

Sub Macro1()
'
' Macro1 Macro
Sheets("CMCS Tracker").Range("A37").Range("A37").Select

Application.ScreenUpdating = False
'
Sheets("Results").ListObjects("Results").ListColumns("Sort").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("A37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Sheets("Results").ListObjects("Results").ListColumns("Program").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("B37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("PartNumber").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("C37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Description").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("D37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("UM").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("E37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Rev Quoted").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("F37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("CommCode").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("G37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Rev Needed").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("H37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("DSRationale").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("I37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Revision").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("J37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Incumbent").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("K37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Last PO#").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("L37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Date of Last PO").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("M37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("Source Type").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("O37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("DRSQuoteID").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("P37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


Sheets("Results").ListObjects("Results").ListColumns("VendorName").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("T37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False





Dim LLRow As Long

LLRow = Sheets("Results").Cells(Rows.Count, "C").End(xlUp).Row

Sheets("Results").Range("R2:AN" & LLRow).Copy
Sheets("CMCS Tracker").Range("U37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Sheets("Results").ListObjects("Results").ListColumns("Max NRE").DataBodyRange.Copy
Sheets("CMCS Tracker").Range("AR37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Sheets("Results").Range("AP2:AR" & LLRow).Copy
Sheets("CMCS Tracker").Range("AS37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Sheets("Results").Range("AT2:CG" & LLRow).Copy
Sheets("CMCS Tracker").Range("AV37").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Application.ScreenUpdating = True

End Sub

Any help is appreciated. Thanks!

 

[mumps]

In order to select a range, the sheet that contains that range must be the active sheet. One way of doing what you want is to first activate the sheet and then select the range.

 

[dmt32]

It is very rare that you need to use Select or Activate in code & is generally best to try and avoid using them where possible - using select will slow down your code and, as you have discovered, give rise to errors if the specified sheet is not the activesheet.

In addition, you can also look at ways to reduce repeating the same lines of code where only part of the code changes for each line. A common practice is to use a loop with changing variable(s) to achieve the same result but with less lines of code.

I have only had quick glance at what you are trying to do & understand that following is not tested & may not be fully correct, but see if the updated code helps you

Option Explicit
Sub Macro1()
    Dim wsCMCS_Tracker  As Worksheet, wsResults As Worksheet
    Dim objResults      As ListObject
    Dim ListColumn      As Variant
    Dim CopyRanges      As Range, CopyRange As Range
    Dim LLRow           As Long, i As Long, c As Long
    
    With ThisWorkbook
        Set wsCMCS_Tracker = .Worksheets("CMCS Tracker")
        Set wsResults = .Worksheets("Results")
    End With
    
    Set objResults = wsResults.ListObjects("Results")
    
    Application.ScreenUpdating = False
    
    For Each ListColumn In Array("Sort", "Program", "PartNumber", "Description", _
                                    "UM", "Rev Quoted", "CommCode", "Rev Needed", _
                                    "DSRationale", "Revision", "Incumbent", "Last PO#", _
                                    "Date of Last PO", "Source Type", "DRSQuoteID", "VendorName", "Max NRE")
        'column index counter
        c = c + 1
        c = IIf(c = 14, 15, IIf(c = 17, 20, IIf(c = 21, 44, c)))
        '
        objResults.ListColumns(ListColumn).DataBodyRange.Copy
        wsCMCS_Tracker.Cells(37, c).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                 SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Next ListColumn
    
    LLRow = wsResults.Cells(wsResults.Rows.Count, "C").End(xlUp).Row
    
    Set CopyRanges = wsResults.Range("R2:AN" & LLRow & ",AP2:AR" & LLRow & ",AT2:CG" & LLRow)
    
    For Each CopyRange In CopyRanges.Areas
        i = i + 1
        CopyRange.Copy
        wsCMCS_Tracker.Cells(37, Choose(i, "U", "AS", "AV")).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                                           SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
    Next CopyRange
    
    Application.ScreenUpdating = True
    
End Sub

Hope Helpful

Dave

 

[gheyman]

Thank you both

 

[mumps]

Glad we could help.