Expert: Adelaide carvalho - 10/29/2009

Question

Hi Adelaide,

I have an Excel Sheet named "Sample Export". if we are getting the word" Data not matching" in the column H, then it should fetch the entire row from there and paste it in another sheet.

Please help me out ..Thanks in advance...

Sandy


Answer
Hi Sandy
How are you?
Please see the following

Option Base 1
Sub copying()
Rem On Error Resume Next
Const LastRow = 200
Dim R As Range, enc As Boolean, LastCol As Integer
Set R = Worksheets("Sample Export").Range("H:H")
I = 1
enc = False
R.Cells(LastRow, 1) = "Data not matching"
Do While enc = False
   If R.Cells(I, 1) = "Data not matching" Then
     
       enc = True
       Else
       I = I + 1
       End If
Loop
If I < LastRow Then
  Dim Sh(2)
  Sh(1) = "Sample Export": Sh(2) = "FollowOne"
  Set R = Worksheets("Sample Export").Range("1:1")
  LastCol = R.Columns.Count
  
  For S = 1 To 2
  Set R = Worksheets(Sh(S)).Range("A" + LTrim(Str(I)))
  For J = 1 To LastCol
      R.Cells(1, J) = "Data not matching"
    Next J
   Next S
End If
End Sub


Cheers
Adelaide