Initiating copy of filtered data from activex control in different sheet

Question

I'm having issues copying and moving filtered data to a new sheet for further evaluation. The goal is to use an activex textbox and command button on sheet1 to filter data on sheet2(Data) and copy the results to sheet3(Calculation). Here's the code I'm working with:

Private Sub CommandButton1_Click()

    Sheets("Data").Range("C2").Value = TextBox1.Text

    Worksheets("Data").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter _
        field:=21, Criteria1:="=*" & TextBox1.Text & "*"

    Range("Table1[[#Headers],[Comp Date]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    Sheets("Calculation").Select
    ActiveSheet.Paste

End Sub

When I run this code on the module for sheet1, I get a "Method 'Range' of object '_Worksheet' failed" error message. However, if I take the bit of code relating to the copy and paste task and place it in the module for sheet2 initiated by a second command button, it works fine. I'd like to accomplish both the filter and copy and paste with the push of one button if possible. Can anyone offer any assistance in where I'm going wrong?

Answer 1

Based on your comments, try this:

Private Sub CommandButton1_Click()
    Dim lo                    As ListObject
    With Sheets("Data")
        .Range("C2").Value = TextBox1.Text

        Set lo = .ListObjects("Table1")
    End With
    With lo
        .Range.AutoFilter field:=21, Criteria1:="=*" & TextBox1.Text & "*"
        .Range.SpecialCells(xlCellTypeVisible).Copy
    End With
    Sheets("Calculation").Paste

End Sub