1

Am trying to copy an entire row from Sheet1 (FA) to Sheet 2 (FABREAK), if the cells in Column A contains the word NPC. I have the following code:

Sub CopyRows()
Dim bottomL As Integer
Dim x As Integer
bottomL = Sheets("FA").Range("A" & Rows.Count).End(xlUp).Row: x = 1

Dim c As Range
For Each c In Sheets("FA").Range("A1:L" & bottomL)
    If c.Value = "NPC" Then
        c.EntireRow.Copy Worksheets("FABREAK").Range("A" & x)
        x = x + 1
    End If
Next c
End Sub

It works when Column A in Sheet FA has just the word "NPC" in it but fails when it contains "FA NPC". Is there a way to go around this problem? Am kinda new to Excel VBA. Any help would be good.

Community
  • 1
  • 1
Sage Mohan
  • 155
  • 1
  • 1
  • 8
  • 2
    `If c.Value Like "*NPC" Then` – Scott Craner Dec 08 '16 at 19:39
  • 3
    Or `If Instr(c.Value, "NPC")>0 Then` – Scott Craner Dec 08 '16 at 19:40
  • @ScottCraner. The second one worked like a charm. Is it possible to copy just the values? At present if any of the cells in FA had formulas, it copies the formulas as well. – Sage Mohan Dec 08 '16 at 19:51
  • 1
    `Worksheets("FABREAK").Rows(x).Value = Sheets("FA").Rows(c.Row).value` – Scott Craner Dec 08 '16 at 19:53
  • FWIW: Are the values you are looking for in multiple columns? Or just in column A? If multiple columns, your code might copy the same source row to multiple destination rows (if, for instance, both column A **and** column D on a single row contained "NPC"), so you may need to skip copying if the current `c.Row` is equal to the value of `c.Row` when the last copy was performed. If just column A, change `For Each c In Sheets("FA").Range("A1:L" & bottomL)` to `For Each c In Sheets("FA").Range("A1:A" & bottomL)`. – YowE3K Dec 08 '16 at 20:02
  • @ScottCraner. Thanks. One last thing. In the second code that I used from your first comment, is it possible to distinguish between PC and NPC? At present, it captures both, but I need it to only capture PC or NPC and not both at the same time. Can that be done? – Sage Mohan Dec 08 '16 at 20:05
  • @YowE3K. The value that am looking for only exists in column A. But that info. is good to know. Thank you. – Sage Mohan Dec 08 '16 at 20:06
  • 1
    `If Instr(c.Value, "PC")>0 And Instr(c.Value, "NPC")<1 Then` – Scott Craner Dec 08 '16 at 20:07

0 Answers0