Match Values and Copy Rows
On Jul 20, 2:22*pm, Joel wrote:
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" wrote:
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.- Hide quoted text -
- Show quoted text -
thanks joel
|