QUESTION: i need a macro that do conditional copy if find "copythis" in column C ,then copy that row in sheet1 and paste in sheet2.
example; A B C banana 1 copythis orange 2 apple 3 copythis
result i want,
A B C
banana 1 copythis
apple 3 copythis
copy from sheet1 and paste in sheet2.
ANSWER: ck,
Sub conditionalcopy() Dim sh1 As Worksheet, sh2 As Worksheet Dim rw As Long, cell As Range, r As Range
Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") rw = 1 Set r = sh1.Range(sh1.Cells(1, 1), sh1.Cells(sh1.Rows.Count, 1).End(xlUp)) For Each cell In r
If InStr(1, cell.Offset(0, 2), "copythis", vbTextCompare) Then cell.EntireRow.Copy sh2.Cells(rw, 1) rw = rw + 1 End If
Next End Sub
tested with similar data and it worked for me.
-- Tom Oglvy
---------- FOLLOW-UP ----------
QUESTION: thank,this work for me,but now i change my mind,i need it copy from column A to column C,not entire row.
ck,
Sub conditionalcopy() Dim sh1 As Worksheet, sh2 As Worksheet Dim rw As Long, cell As Range, r As Range
Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") rw = 1 Set r = sh1.Range(sh1.Cells(1, 1), sh1.Cells(sh1.Rows.Count, 1).End(xlUp)) For Each cell In r
If InStr(1, cell.Offset(0, 2), "copythis", vbTextCompare) Then cell.resize(1,3).Copy sh2.Cells(rw, 1) '<== this line changed rw = rw + 1 End If
Next End Sub
Advertisement