Excel VBA to check other sheet and copy over information

  • Hello,


    I am new to VBA and I am completely stuck on this code. I've tried loads of different threads and methods but with no luck. Please help!


    Bit of background. One sheet holds customer info and a date of when a letter has been issued, a column to check the number of weeks since issued (formula already set up), and another column to add a date once the letter has been returned.


    My spreadsheet contains several sheets but the 2 I am using for this code are called "CHECKLIST" & "REMINDERS". I want the code (which I intend to have a button for on the REMINDERS sheet) to see if a reference from "CHECKLIST" column B is in "REMINDERS" column B (they won't be in the same order). If it is, then do nothing/ move to the next one. If it is not, then I need to check if the data in the row (with the new reference) matches some criteria. These being if a date is in CHECKLIST Column H, number of weeks >3 in column I and nothing in column J (this is where the response date would be held if we had had one). If these are all true then I want 8 cells from the row copied from CHECKLIST on to the REMINDERS sheet.


    I am planning to have it so the user can hit the command button to see which customers have not yet responded after 3 weeks so that a reminder can be issued. If they hit the button and there are no new cases then a message to say "No new reminders required" would be great!


    I have the following formula but after hours of trying I cannot get it to work.



    Massive thanks in advance!

    Cheers

    Seb

  • Hello and Welcome to the Forum :)


    You do not indicate what your macro does produce in terms of results ...;)


    Best of all ... to avoid useless guess work ... is to attach a sample file with just the two worksheets you are concerned with ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim, thanks for your quick reply!


    Unfortunately the macro isn't doing anything at all as I've tweaked it so many times based on various threads :( I know that's not very helpful but my thinking was it could at least provide a base.


    I've attached the template - thanks again all!


    FYI - column A is a unique ref for the two linked people. Column B is a 'true' unique ref for for that individual. There are 1000 rows on the checklist sheet


    Cheers

    Seb

  • Based on your sample data ... and my understanding of your request ...


    Probably a simple Autofilter could generate the records to be copied to your Reminders sheet ...


    Is the attached test file producing the right output ...?

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The command is adding filters but I have the below points:


    - I'd like the command button to be on the Reminders tab

    - Column H on the checklist sheet could be blank as the letters may not have been issued yet

    - the test filter is filtering to show column J where there is a response date. I'm after one that shows who has had a letter (a date in column I), passed 3 weeks (column J) but not yet responded (so nothing in column J)


    Hope hat makes sense


    Cheers

    Seb

  • OK to recap ...


    You mean for Column I criteria is >3 ... and for Column J criteria is (Blanks) ...


    Is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad to hear this could help you out :)


    Thanks a lot for your Thanks ...AND for the Like :thumbup:


    As far as "No Rows Filtered" is concerned ... it is only to avoid the macro to crash ... should you find yourself with not a single record after the filtering process ... Hope this clarifies ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi, I tried the code on the live data and it appeared to work perfectly. I have, however, now noticed that when there are "no records found", the code is not resetting the filters.


    The filters are taken off where there are cases present matching the criteria, just not when there are none. Please advise on how to resolve this,


    Thanks

    Seb

  • Hello,


    Below is the requested modification

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)