If a cell meets a condition, copy that row to another worksheet

  • Re: If a cell meets a condition, copy that row to another worksheet


    It is likely you have not gotten a reply because your post does not provide enough detail.
    What are the conditions?


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments -- use ZIP compression if necessary to meet file size limitations

  • Re: If a cell meets a condition, copy that row to another worksheet


    Hoping this explains a little better (attached an example workbook) and someone can help.

    • Basically worksheet 1 (Details Sheet) gets periodically updated with rows.
    • A user can then generate a report which is worksheet 2 by clicking the 'Generate Report' button.
    • When the button is clicked I would like the first column to be searched for either 1, 2, 3, or 4 (a combobox handles this)
    • When this condition is met, that row then gets copied across to worksheet 2 (Report Sheet)
    • The whole action starts when the 'Create Report' button is clicked on the userform

    Hope this explains a bit better.


    Many thanks for looking.


    [ATTACH=CONFIG]40299[/ATTACH]

  • Re: If a cell meets a condition, copy that row to another worksheet


    Hi stickyfeet,


    Paste the following into the code of your UserForm1.


    Instead of copying and pasting each row it stores the data in memory, makes decisions based on your criteria and creates another array that it will paste all at once into your report sheet. This is so that if you happen to have a significant amount of rows it still should run quite zippy.


    Some things to keep in mind here are:

    • This will not copy cell formats to the report.
    • I noticed you had pre-defined formats applied to your report sheet, this code clears that format on those cells that were already written to on the report. This could be fixed either by deleting those rows instead of clearing them (could be slow) or by reapplying the format after the code has run (before unload).
    • I included a function for determining the last row of a worksheet, this isn't really necessary but over the years I've found that determining the last row of a worksheet can be... annoying. This is the function I currently use 99% of the time because as well as determining the last row using the UsedRange property it also deletes empty rows that UsedRange sometimes misinterprets as used.
    • An advanced filter might be something else to look into.

    If I didn't make any sense at all or you have any questions, ask away.


    Cheers,
    MJ


  • Re: If a cell meets a condition, copy that row to another worksheet


    This code is working perfectly, so much so that I'm trying to apply it to another workbook.


    I'm trying to work through it to try and determine what is going on, but I'm not getting very far.


    So I'm going to be cheeky and ask for a little more help please.


    Rather than looking at the first column, I'm trying to get the code to look at another column. Then do exactly the same thing by copying the whole line.


    I've been looking at 'lLocation' as this is where it gets the value from the combobox, then trying to work out how/where it determines which column to look at.


    Can you help please?


    Thanks

  • Re: If a cell meets a condition, copy that row to another worksheet


    Hi stickyfeet,


    So I've altered the code a bit to make it a little more general and customizable. Firstly to answer your question:


    Quote


    I've been looking at 'lLocation' as this is where it gets the value from the combobox, then trying to work out how/where it determines which column to look at.


    The code was choosing which column to look at on this line:


    Code
    If vData(lRow, 1) = vTestValue Then


    The '1' means it's checking against the first column in vData which is the array created from the data range. Also, lLocation was getting its value from the rightmost character of your combobox because of how the values were hardcoded in, this isn't really necessary as you'll see in the updated code.


    So, I changed the code so that it lets you choose which column you would like to filter on and then dynamically updates the combobox with a unique and somewhat sorted (works kind of odd on text) list of values from that column. The subroutines to create the unique list and sort it are at the bottom (some of my favourites).


    I also changed two more things:

    • Instead of clearing the report beforehand I decided to delete the used rows.
    • The way the data values are tested against the filtering value was changed to convert them both to text beforehand, just incase one is converted to text at some other point while being passed to and from the userform.

    So here's a copy of the code to look at but I changed your Userform a bit to allow for my changes so I'll also include the .frm and .frx files so you can just import it into your spreadsheet. If you have any questions, feel free to ask.


    Cheers,
    MJ


    [ATTACH=CONFIG]40333[/ATTACH]


  • Re: If a cell meets a condition, copy that row to another worksheet


    Hi and thanks again. Works perfectly! Also been spending a bit of time going through the code and trying to work out what's going on.


    I've reused the code several times in different workbooks/projects. So once again a big thanks for your time.


    Regards


    :)

  • Re: If a cell meets a condition, copy that row to another worksheet


    Hi, hoping LeastAction is around.


    I'm trying to do something similar to the above by reusing your code. I'm stuggling with it :( so was hoping you can help again.


    Same kind of worksheet but there is a number in Column 'S' and when that number is met (users selects) then I need that row copied across to a new worksheet.


    I kind of (well I think) I understand bits of the code but getting it to look in 'S' just doesnt seem to work.


    Big thanks!

  • Re: If a cell meets a condition, copy that row to another worksheet


    Please do not post questions in threads started by other members.


    If you have a query then start your own thread, give it an accurate and concise title that summarises your problem and explain your issue fully.


    If your question relates to this (or any other) thread, then include a link by copying the URL from the address bar of your browser and pasting into your message.


    Make sure you explain exactly the changes needed and how you would see them working. The code in this thread relates to selections from a combobox but you "dont want to use combo box" - please explain the process you have in mind.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!