Copy Data to new Worksheet Based on Column Value

[slpswhite]

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.

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

 

[slpswhite]

That one really makes no sense to me. But it works!Thanks!

 

[Fluff]

It's simply copying from row 9, rather than row 8

 

[slpswhite]

Hey Fluff I hope all is well. I haven't had much time to work with this lately. The code you provided above works fine, but I need Row 8 to be part of the data which is searched. If I change the code to:

Sheets("Master Table").Range("B8:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(1, 0)

It always includes Row 8 in the returned data. If I search on the Application # in Row 8 on the Master Table I get a request to save the spreadsheet. If I cancel it displays the correct data. Very strange.

Any ideas?

Here is the whole code as it currently is:

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
    Application.ScreenUpdating = False
    LastRow = Sheets("Master Table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Master Table").Range("B8:B" & LastRow).AutoFilter Field:=1, Criteria1:=ApplicationNumber
    On Error Resume Next
    Sheets("Master Table").Range("B8:B" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("SearchMasterTable").Cells(Sheets("SearchMasterTable").Rows.Count, "A").End(xlUp).Offset(1, 0)
    On Error GoTo 0
    If Sheets("Master Table").AutoFilterMode = True Then Sheets("Master Table").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Sounds like you have some event code running.
Have a look in the sheet module of Master Table & also lookin ThisWorkbook module