+ Reply to Thread
Results 1 to 14 of 14

Extracting entire row to new sheet based on criteria

  1. #1
    Registered User
    Join Date
    11-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    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. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    11-19-2013
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    29

    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?
    Last edited by johnplyer; 11-19-2013 at 02:22 PM. Reason: more info

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    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. #6
    Registered User
    Join Date
    11-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #7
    Registered User
    Join Date
    11-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    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)
    line you had?


    Many Thanks!

  8. #8
    Registered User
    Join Date
    11-21-2013
    Location
    hyd
    MS-Off Ver
    Excel 2010
    Posts
    1

    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. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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. #10
    Registered User
    Join Date
    11-19-2013
    Location
    Maynardville, TN USA
    MS-Off Ver
    Excel 2013
    Posts
    5

    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. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    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. #12
    Registered User
    Join Date
    11-19-2013
    Location
    Maynardville, TN USA
    MS-Off Ver
    Excel 2013
    Posts
    5

    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. #13
    Registered User
    Join Date
    04-16-2015
    Location
    Frankfurt
    MS-Off Ver
    2012
    Posts
    2

    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. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extracting Unique Records and Copying into Another Sheet Based on Various Criteria
    By abutler911 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2013, 11:09 AM
  2. Copy entire row from Master sheet based on two criteria
    By melispol44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2013, 08:07 PM
  3. How to copy an entire row from one sheet to another based on criteria
    By Mian USman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2013, 04:17 AM
  4. Help - Extracting an entire row that fits a single criteria
    By Babyanne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2009, 06:40 PM
  5. [SOLVED] Copy entire row to another sheet based on a criteria
    By Brig Siton in forum Excel General
    Replies: 3
    Last Post: 08-07-2006, 04:05 PM

Bookmarks

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