Copy rows based on a condition...revisited

Tim - Feb 22, 2011 at 05:42 PM
 Tim - Mar 8, 2011 at 03:40 PM
This question was asked and answered by HELPER.
(I'll attach it below as I cannot find a reference number)
My addendum to the question is;
Where would I put the code? under the general for the workbook?, or on the sheet?
My situation is similar, but expanded.
I have a workbook with a master sheet and 16 additional sheets that I plan to email to my sales reps. So with the solution provided by HELPER I should be able to modify the code for additional sheets, but do I put the code in the master sheet or the sub sheet(s)?
Maybe I'm assuming to much as I am new to VB.
Here is the original question/response:

How can I have excel copy the entire row of data from worksheet Employee Inventory to another worksheet called EEs if column Q contains TERM. I've tried almost everything but nothing is working. Please help!

Assumptions for this code:
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. EEs sheet will have headings in row one so the data will start copying in row 2.

Set i = Sheets("Employee Inventory")
Set e = Sheets("EEs")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("Q" & j))

If i.Range("Q" & j) = "TERM" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 24, 2011 at 09:33 AM
Hi Tim,

Put the code in the master sheet / this workbook if you want the code to be applied to the whole workbook. Like custom functions or a conditional format code for more then three conditions etc....

Put the code in the sub sheet / sheet1 (sheet1) etc.. if you want the code to run automatically.

Put a code in a module if you want to run the code manually.

So basically you will have to ask yourself if you want the code to run automatically or manually.

Best regards,
I just got back into town.
I'll try it out.