# Extracting entire row to new sheet based on criteria

1. ## Extracting entire row to new sheet based on criteria

Hi,

I have tried to search on a lot of web sites and seems like this can only be done with VBA/Macro, which I have no knowledge with it. I can record a Macro and that's it.

I would like to see if there is any functions that I can put in a column on the original worksheet, something like

=IF(F3=Yes, [a formula to copy entire row 3 to destination sheet], " ")

so if F3 is yes, then it will copy the data on that row to the destination sheet, and it should be on the first EMPTY row. So it will not overwritten data that was copied before, say if F6=YES, then it will automatically copy the row to the next row and skip data on F4, F5 because they =NO

Thank you!

2. ## Re: Extracting entire row to new sheet based on criteria

Use a formula like this (let's say in H3):

=IF(F3="Yes",MAX(F\$2:F2)+1,"-")

then copy that down beyond your data (until the hyphens start to show). Then in H2 on the destination sheet you can have this formula:

=IFERROR(MATCH(ROWS(\$1:1),Source!H:H,0),"-")

and this one in A2:

=IF(OR(\$H2="",\$H2="-"),"",INDEX(Source!A:A,\$H2))

then copy this across to column F. Then you can copy all the formula from row 2 down the sheet until you get hyphens in column H.

Hope this helps.

Pete

3. ## Re: Extracting entire row to new sheet based on criteria

Here is a vba solution. Do you want to copy all rows in the sheet that have a yes in column F. Is that your criteria or is there something else?

If this is the case then:

``Please Login or Register  to view this content.``
How to install your new code
1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

4. ## Re: Extracting entire row to new sheet based on criteria

what if I wanted to move the row of data based on completion, so when someone puts in a completion date, that line is complete and transfers to a "Completed" sheet? what would I need to change in the code above?

5. ## Re: Extracting entire row to new sheet based on criteria

Please read the Forum Rules at the top of the screen - you should not post a question in someone else's thread, but start your own instead. In necessary, include a link back to this thread.

Pete

6. ## Re: Extracting entire row to new sheet based on criteria

Hi Pete,

Thank you for the help. I got the H on source sheet showing 1 (with Yes) and "-" (with else)
However, the destination sheet, it only shows "-" on the whole column.
I might not have done it right.

Should I copy the third formula into whole column F and A2?

Thank you!

7. ## Re: Extracting entire row to new sheet based on criteria

Hi Alan,

That seems working, even I am newbie in VBA, thank you so much! I am going to add some other things on the Macro and hopefully it will work.
If I want it to scan column F on 2 sheets and copy to 3rd sheet.

Should I add another Dim for worksheet, another length to the code,
or do I simply copy the whole code and change the sheet name, and probably the Dim name etc?

Also I am trying to copy only the latest one, I guess that means it wouldnt copy what was already copied last time.

I am thinking to make it run a remove duplicate in the Macro, not sure if it is the best idea tho. Any input?

It turns out this is a bigger project than I thought...

Maybe I should explain a little more what I actually want it to do.
This workbook is to keep track of different types of shipments we have (Sheets"Air" for Air shipment, sheets"Ocean" for ocean shipment)
some of these shipments have claims and we will put in Yes in the column F (Row 2 is header, actual shipment data start from row 3)
and when there are claims, this function/formula/vba I run can help me to extract the shipments to Sheets"Claim"
and this function, ideally, only copy the latest updated shipment.

also they actually dont need the entire row, just a few specific column, i was thinking i can record removing some columns in the macro, I guess that would mess up the columns when I run the function and copied the whole row next time, should I use below code to copy the specific columns? say if F8=Yes, then copy D8, E8, L8, to claim sheet

ws.Range("D" & i).Copy wt.Range("D" & lt +1)
ws.Range("E" & i).Copy wt.Range("E" & lt +1)
ws.Range("L" & i).Copy wt.Range("L" & lt +1)

to replace the
ws.Range("F" & i).EntireRow.Copy wt.Range("A" & lt + 1)

Many Thanks!

8. ## Re: Extracting entire row to new sheet based on criteria

hi alansidman,

i got your formula working..however

1. i want only select column of the row to be copied
2. i want first few lines in the new sheet to be empty for some manual writing

9. ## Re: Extracting entire row to new sheet based on criteria

@amerns

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

10. ## Re: Extracting entire row to new sheet based on criteria

I thank everyone for the replies. am sorry I didn't think anybody was replyi8ng because I wasn't getting notifications and was busy doing other things and not checking.
I tried both VBA and the formulas but neither worked. I am not very well versed with excel formulas so I am sure I may be doing something wrong. I have changed the plan of action and will have to post a similar request as I get it laid out. Thanks again...

11. ## Re: Extracting entire row to new sheet based on criteria

@balyra

I think you have posted to the wrong thread. This is the thread that you started last November:

http://www.excelforum.com/excel-form...ml#post3480398

and you haven't received any notifications because no-one has replied to that thread.

Hope this helps.

Pete

12. ## Re: Extracting entire row to new sheet based on criteria

Thanks Pete, duh, I guess I wasn't paying attention, sorry. But the information on this thread has helped me greatly with the issue I was having back then and now too so I'm thankful I saw it.

13. ## Re: Extracting entire row to new sheet based on criteria

Hello,

I have applied this code to my sheet and it works perfectly however what are the modifications that i need to make if i would like to extract to different sheets. I.e i have 4 different words in column B in my data sheet and i would like to extract the entire row to a different sheet depending on what it states in it. So if it says Asia in column B then extract it to the Asia sheet, if it says Europe then extract it to Europe sheet and so on.

Thank you

14. ## Re: Extracting entire row to new sheet based on criteria

@Ru71an
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1