I have a worksheet called Master Table with columns A-CR. The Column I am searching is in column B. In the same workbook I have a spreadsheet called SearchMasterTable I am using cell C1 in this workbook for the input of the requested lookup.
If the data in SearchMasterTable cell C1 equals one or multiple records in workbook Master Table column B, I am trying to copy these records to the workbook SearchMasterTable starting with row 6.
The code runs but is not copying the data to the new worksheet.
If the data in SearchMasterTable cell C1 equals one or multiple records in workbook Master Table column B, I am trying to copy these records to the workbook SearchMasterTable starting with row 6.
The code runs but is not copying the data to the new worksheet.
Code:
Sub finddata()
Dim ApplicationNumber As String
Dim finalrow As Integer
Dim i As Integer
Dim LastRow As Long
Sheets("SearchMasterTable").Range("A6:CR506").ClearContents
ApplicationNumber = Sheets("SearchMasterTable").Range("C1").Value
finalrow = Sheets("Master Table").Range("A10000").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 2) = ApplicationNumber Then
'Sheets("Master Table").Range(Cells(i, 1), Cells(i, 96)).Copy
'Sheets("SearchMasterTable").Range("A6").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
Application.ScreenUpdating = False
LastRow = Sheets("Master Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=2, Criteria1:="=ApplicationNumber"
On Error Resume Next
Sheets("Master Table").Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(2, 0)
On Error GoTo 0
If Sheets("Master Table").AutoFilterMode = True Then Sheets("Master Table").AutoFilterMode = False
Application.ScreenUpdating = True
End If
Next i
Range("C1").Select
End Sub