Help with writing this code.

Hi All,

I've just got to grips with the basics of VBA but I need your expertise with a particular code. Looking at the data below, I would like the code to be written so if the the answer is yes is Column D (contractor info) I want the entire row to be cut and added to sheet 2. Sheet 2 would have the same headings. there will be no blanks at all.

District Work Request Audit Result Contractor Info provided
21 39NGWS D4 yes
81 01MNRK D4 No
81 01MQQZ D5 No
81 01MSCF D7 Yes
12 37JZLW D8 Yes

I'd be grateful if someone could help me with this.

thanks in advance

sorry the format changed so it would be something like this

District WR AR Contractor info
12 3654774LQ D1 Yes

try like

vb Code:

dim sumrows as range, c as range
nextrow = sheets("sheet2").cells(sheets("sheet2").rows.count, 1).end(xlup).row + 1
 
for each c in range("d:d")
   if isempty(c) then exit for    ' finish on empty cell
   if c.value = "Yes" then 
      if sumrows is nothing then 
          set sumrows = c.entirerow
         else
          set sumrows = union(sumrows, c.entirerow)
      end if
   end if
next
sumrows.Copy Sheets("sheet2").Range("a"  & nextrow)
sumrows.Delete xlShiftUp

Quote:

---

Originally Posted by westconn1

try like

vb Code:

dim sumrows as range, c as range
nextrow = sheets("sheet2").cells(sheets("sheet2").rows.count, 1).end(xlup).row + 1
 
for each c in range("d:d")
   if isempty(c) then exit for    ' finish on empty cell
   if c.value = "Yes" then 
      if sumrows is nothing then 
          set sumrows = c.entirerow
         else
          set sumrows = union(sumrows, c.entirerow)
      end if
   end if
next
sumrows.Copy Sheets("sheet2").Range("a"  & nextrow)
sumrows.Delete xlShiftUp

---

Thank you very much for this.

I copied and pasted this in the workbook but to no avail. Please forgive my ignorance but I'm unable to run this macro when I press F5. As I said I'm a novice so please be patient with me.

Put the posted code within Sub MyCode() and End Sub lines in a module. Run it by pressing F5 within the Visual Basic editor, or from the workbook via Tools -> Macro menu.

Quote:

---

Originally Posted by His Nibbs

Put the posted code within Sub MyCode() and End Sub lines in a module. Run it by pressing F5 within the Visual Basic editor, or from the workbook via Tools -> Macro menu.

---

Hi,

I did and it came with the following error:

Runtime Error '450'

wrong number of arguments or invalid property assignment

please let me know.

Quote:

---

wrong number of arguments or invalid property assignment

---

dunno, which line?
i tested before posting, show the code as you have it

Quote:

---

Originally Posted by westconn1

dunno, which line?
i tested before posting, show the code as you have it

---

This is what it looks like.

it would help if you answered the question

Quote:

---

which line?

---

though from your picture you have no sheet with name of sheet2, change code to suit