Match Values and Copy Rows
 

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.

Match Values and Copy Rows
 

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.

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

Match Values and Copy Rows
 

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


"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.