Macro with conditional copy

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:46 PM GMT

Question

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.

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.resize(1,3).Copy sh2.Cells(rw, 1)  '<== this line changed
  rw = rw + 1
End If

Next End Sub

Advertisement

©2017 eLuminary LLC. All rights reserved.