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

[Damian37]

Hello All,
I'm not entirely sure what is going on. I used the below code successfully, but when I attempted to assign the code to a button on the spreadsheet I received the Run-time error. I'm not entirely sure why the code would suddenly stop working. The code I'm using is below:

Sub Beltway()


   Dim TableSheet As Worksheet
   Dim NewWB As Workbook
 
   Set TableSheet = Worksheets("AllData")
 
   With TableSheet
 
      .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Beltway"
      .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open*"
      .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM; 2.5", "XM; 2.6", "XM; 3", "XR", "XM; 1", "XM; 2", "Mall Kiosk", "XM", "Kiosk", "3.0", "XR Lite", "Legacy Pre-Paid", "Pop Up", "Pop-Up", "PR", "Sat Loc 3351"), Operator:=xlFilterValues
   
     [B] .Range("Table6[[#Headers],[Store Id]]").Select[/B]
      .Range(Selection, Selection.End(xlToRight)).Select
      .Range(Selection, Selection.End(xlDown)).Select
 
   End With
 
   Selection.Copy
   Set NewWB = Workbooks.Add
 
   With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
 
   NewWB.SaveAs Filename:="C:\Users\DVelez202\Desktop\POC Validation-Request\Non-BP Beltway POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
             

End Sub

The line with Bold codes within my code is where I'm sent to when I click on "Debug", but as I stated before, the code was working just fine, and I didn't make any changes other than trying to assign the code to a form button within the spreadsheet I'm using. Very confused.

D.

 

[Damian37]

Hello,
I figured out why I was receiving the above error code, and now when I run the code manually it runs without issue. However, when I attempt to create a form button to call the sub code that I have, I receive the above error message again. I'm not sure why the code would work manually, but not work when I attempt to run it using the form button. Any ideas would be greatly appreciated. Thank you.

D.

 

[MARK858]

Is the button on the Worksheets("AllData") sheet?

 

[Damian37]

Is the button on the Worksheets("AllData") sheet?

No. The button is located on another tab within the workbook.

 

[MARK858]

You can't use Select on a sheet other than the active sheet. The ActiveSheet is the sheet with your button when you click it .

 

[Damian37]

You can't use Select on a sheet other than the active sheet. The ActiveSheet is the sheet with your button when you click it .

Not sure I understand your response. When I run the code manually, which is to say, my Call Sub the code works just fine. I only receive this message when I attempt to use the button I created. As you can see from the above code, I'm setting the Tablesheet with the tab the data is contained in. I don't think I'm fully understanding your response. The button should only be running the the Call Sub, which should then run the code below it. Am I missing something?

 

[MARK858]

When you run the code manually is Worksheets("AllData") the Active sheet?

Select can only be used on the active sheet. If you are using Select for a sheet that is not the active sheet then it will error.

You are clicking a button on a sheet other than Worksheets("AllData") which makes the other sheet active but your code is trying to do a select on Worksheets("AllData"), which isn't the active sheet.

 

[Alex Blakenburg]

There are probably better ways of doing this but try this, it removes the select statements, which @MARK858 pointed out is what is most likely causing your button to fail.
Note: If StoreID is actually the first column, you could simplify this further.

Sub Beltway()
    
    Dim TableSheet As Worksheet
    Dim NewWB As Workbook
    
    Dim colStoreID As Long
    Dim colLast As Long
    Dim rngTblCopy As Range
    
    
    Set TableSheet = Worksheets("AllData")
    
    With TableSheet.ListObjects("Table6")
            .Range.AutoFilter Field:=3, Criteria1:="Beltway"
            .Range.AutoFilter Field:=5, Criteria1:="Open*"
            .Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM; 2.5", "XM; 2.6", "XM; 3", "XR", "XM; 1", "XM; 2", "Mall Kiosk", "XM", "Kiosk", "3.0", "XR Lite", "Legacy Pre-Paid", "Pop Up", "Pop-Up", "PR", "Sat Loc 3351"), Operator:=xlFilterValues
            colStoreID = .ListColumns("Store ID").Range.Column
            colLast = .Range.Columns.Count
            Set rngTblCopy = .Range.Columns(colStoreID).Resize(, colLast - colStoreID + 1)
        
    End With
        
        rngTblCopy.Copy
        Set NewWB = Workbooks.Add
        
        With NewWB.Worksheets(1)
           .Paste
           Application.CutCopyMode = False
           .Cells.EntireColumn.AutoFit
        End With
        
        NewWB.SaveAs Filename:="C:\Users\DVelez202\Desktop\POC Validation-Request\Non-BP Beltway POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                     FileFormat:=xlOpenXMLWorkbook, _
                     CreateBackup:=False
             
End Sub

 

[Damian37]

There are probably better ways of doing this but try this, it removes the select statements, which @MARK858 pointed out is what is most likely causing your button to fail.
Note: If StoreID is actually the first column, you could simplify this further.

Sub Beltway()
   
    Dim TableSheet As Worksheet
    Dim NewWB As Workbook
   
    Dim colStoreID As Long
    Dim colLast As Long
    Dim rngTblCopy As Range
   
   
    Set TableSheet = Worksheets("AllData")
   
    With TableSheet.ListObjects("Table6")
            .Range.AutoFilter Field:=3, Criteria1:="Beltway"
            .Range.AutoFilter Field:=5, Criteria1:="Open*"
            .Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM; 2.5", "XM; 2.6", "XM; 3", "XR", "XM; 1", "XM; 2", "Mall Kiosk", "XM", "Kiosk", "3.0", "XR Lite", "Legacy Pre-Paid", "Pop Up", "Pop-Up", "PR", "Sat Loc 3351"), Operator:=xlFilterValues
            colStoreID = .ListColumns("Store ID").Range.Column
            colLast = .Range.Columns.Count
            Set rngTblCopy = .Range.Columns(colStoreID).Resize(, colLast - colStoreID + 1)
       
    End With
       
        rngTblCopy.Copy
        Set NewWB = Workbooks.Add
       
        With NewWB.Worksheets(1)
           .Paste
           Application.CutCopyMode = False
           .Cells.EntireColumn.AutoFit
        End With
       
        NewWB.SaveAs Filename:="C:\Users\DVelez202\Desktop\POC Validation-Request\Non-BP Beltway POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                     FileFormat:=xlOpenXMLWorkbook, _
                     CreateBackup:=False
            
End Sub

Thank you very much Alex! This worked like a charm. I will be sure to keep a copy of this code handy. Thank you again!

D.

 

[Alex Blakenburg]

Thank you for letting us know. Glad we could help.