Match Values and Copy Rows

[K]

Hi all, I have two Sheets in a Workbook. One Sheet name is "Data" and
the other Sheet name is "Search". In Sheet "Search" I have three
TextBoxes and in top cell of each TextBoxe I have put headings like,
TextBox1 heading is "Serial" , TextBox2 heading is "Record" and
TextBox3 heading is "Quantity". In Sheet "Data" i have data from
Column A to Column F. In Column A i put "Serials" , in Column B i put
"Record no." and in column C i put "Quantity". In other Columns (D to
F) i have text and other sort of data. I want macro on a button which
should check values in each TextBox of Sheet "Search" and match those
values in Columns A to C of Sheet "Data" and if that value string
match then macro should copy that row from Column A to Column F of
Sheet "Data" into Sheet "Search" For example if i put value "334" in
TextBox1 and "SC0001" in TextBox2 and "45" in TextBox3 then macro
should match TextBox1 value in Column A of Sheet "Data" and TextBox2
value in Column B and then TextBox3 value in Column C and if all three
values get matched in Sheet "Data" columns on same row then macro
should copy that row into Sheet "Search". I hope i was able to
explain my question. Is there any friend can help me on this.

 

[Joel]

try this

Sub Macro1()
'
' Macro1 Macro
'
With Sheets("Sheet2")
Set Serial = .TextBoxes("Textbox 1").Value
Set Record = .TextBoxes("Textbox 2").Value
Set Quantity = .TextBoxes("Textbox 3").Value

With Sheets("Sheet1")
'turn off autofilter
If Worksheets("Sheet1").AutoFilterMode Then
Selection.AutoFilter
End If

.Columns("A:C").AutoFilter Field:=1, Criteria1:=Serial
.Columns("A:C").AutoFilter Field:=2, Criteria1:=Record
.Columns("A:C").AutoFilter Field:=3, Criteria1:=Quantity

.Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _
Destination:=Sheets("Sheet2").Cells


'turn off autofilter
If Worksheets("Sheet1").AutoFilterMode Then
Selection.AutoFilter
End If
End With

End With
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Selection.AutoFilter
Columns("A:B").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
Selection.AutoFilter
Range("F13").Select
ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="14"
End Sub

 

[K]

try this

Sub Macro1()
'
' Macro1 Macro
'
With Sheets("Sheet2")
   Set Serial = .TextBoxes("Textbox 1").Value
   Set Record = .TextBoxes("Textbox 2").Value
   Set Quantity = .TextBoxes("Textbox 3").Value

   With Sheets("Sheet1")
      'turn off autofilter
      If Worksheets("Sheet1").AutoFilterMode Then
         Selection.AutoFilter
      End If

      .Columns("A:C").AutoFilter Field:=1, Criteria1:=Serial
      .Columns("A:C").AutoFilter Field:=2, Criteria1:=Record
      .Columns("A:C").AutoFilter Field:=3, Criteria1:=Quantity

      .Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _
         Destination:=Sheets("Sheet2").Cells

      'turn off autofilter
      If Worksheets("Sheet1").AutoFilterMode Then
         Selection.AutoFilter
      End If
   End With

End With
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.AutoFilter
    Columns("A:B").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.AutoFilter
    Range("F13").Select
    ActiveSheet.Range("$A$1:$C$14").AutoFilter Field:=2, Criteria1:="14"
End Sub





- Show quoted text -

thanks joel

 

[Mota]

Joel

Permit me to ask you for some help:
CHecking your code and comparing wuith my demand, I would like to have your
support on the folowing purpose:

I have a huge spreadsheet with a lot of information.

I need to compare two diffents columns and paste the result in another
column.:
i.e.: If(cell A1 = B1, Paste in C1 = "OK", If not Paste "NOK"

I understand that I can have this using Excel formula, but I need to have a
macro in order to I need to include this solution inside another Macro Code.


I really appreciate if you could help me.

Thanks and regards

Mota