I currently have two sheets: Sheet1 and Sheet2.

Sheet1 one consists of system information for items (Source) and Sheet2 is the Destination Sheet (Target).

I need to be able to filter Column A (Source) for the value which is typed into Cell A3 on the Target Sheet and then paste the data into the first available row in the Target sheet. It seems to be failing on the final line of code I'm not really sure why. Appreciate any help.

The error i get is : Run-time error '1004': Method 'Range' of object'_Worksheet' Failed

Sub CopyRowAndBelowToTarget()

Dim wb As Workbook
Dim src As Worksheet
Dim tgt As Worksheet
Dim match As Range

Set wb = ThisWorkbook
Set src = wb.Sheets("Sheet1")
Set tgt = wb.Sheets("Sheet2")

Dim lastCopyRow As Long
Dim lastPasteRow As Long
Dim lastCol As Long
Dim matchRow As Long
Dim findMe As String

Sheets("sheet2").Activate



' specify what we're searching for
findMe = Range("B1").Value

'Filter column for value
src.Range("A1").AutoFilter Field:=1, Criteria1:=findMe

' find our search string in column A (1)
Set match = src.Columns(1).Find(What:=findMe, After:=src.Cells(1, 1), _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

' figure out what row our search string is on
matchRow = match.Row

' get the last row and column with data so we know how much to copy
lastCopyRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
lastCol = src.Cells(1, src.Columns.Count).End(xlToLeft).Column

    
' find out where on our target sheet we should paste the results
lastPasteRow = tgt.Range("A" & src.Rows.Count).End(xlUp).Row

' use copy/paste syntax that doesn't use the clipboard
' and doesn't select or activate
src.Range(Cells(matchRow, 1), src.Cells(lastCopyRow, lastCol)).Copy _
    tgt.Range("A" & lastPasteRow)