0

I have agreed to help out with some small project at my work that requires a VBA knowledge. I have worked previously with SQL and I thought the VBA is quite similar but I have stuck.

I need a code for the command button to search for the string/text data specified in text box. It will search for data in cells in column B in two worksheets and copy and paste entire rows where the required data was found to the third worksheet.

The searched data can be recorded few times in each worksheet so it will have to loop and search through whole two worksheets and copy and paste all information's related to the search data.

The headers in worksheets one and two (where data are stored) and worksheet three (into where data will be pasted) are the same. The ranges for two worksheets where data are stored are entire worksheets apart from A1:J1 (the headers) and for the third worksheet where the data going to be pasted the range is A11:J1500.

0m3r
  • 12,286
  • 15
  • 35
  • 71
user3212027
  • 1
  • 1

1 Answers1

0

This may help get you started:

Private Sub SearchButton_Click()
    Dim searchTerm As String, rng1 As Range, rng2 As Range, searchRange As Range, cl As Range

    Set rng1 = Worksheets("Sheet1").Range("B1:B" & Worksheets("Sheet1").Range("B2").End(xlDown).Row)
    Set rng2 = Worksheets("Sheet2").Range("B1:B" & Worksheets("Sheet2").Range("B2").End(xlDown).Row)

    searchTerm = Worksheets("Sheet1").TextBox.Text

    searchAndCopy rng1, searchTerm
    searchAndCopy rng2, searchTerm

End Sub

Private Sub searchAndCopy(searchRange As Range, searchTerm As String)
    Dim nextRow As Long

    nextRow = Worksheets("Sheet3").Range("A1").End(xlDown).Row + 1

    For Each cl In searchRange
        If cl = searchTerm Then
            cl.EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & nextRow)
            nextRow = nextRow + 1
        End If
    Next cl
End Sub
Alex P
  • 12,249
  • 5
  • 51
  • 70