Macro to select rows with appropriate dates and move them into a new worksheet

I have an actions register that needs updating on a weekly basis. Rather than manually cutting and pasting for mailouts, I'd like to be able to run a macro each week that will select the rows closest to today's date in the master list and move them into a different worksheet for mailing.

I've recorded macros before, but after several attempts I now think I need to do it with VBA and I'm a complete novice at that. I've tried using and editing some examples from various forums, but none of them do what I want - possibly because I don't know what I'm doing..... I'm planning to do some online courses, but would really appreciate some help now.

If it helps, the sheet I'm working from is labelled Action Register, the date is in column K, and the sheet I want the rows to go to is labelled Weekly updates - any help really appreciated!

Please clarify "closest to today's date".

Hello Catteeya

apart from the answer to stnkynts question could you post an example of the spreadsheet you are working with. That will help us provide some assistance

jmac1947

Hi there both of you

"Today" is the day the macro is run - this will usually be a Friday, as updates need to be in by cob Monday.
I've attached an example of the sheet (I hope) - what I need is a macro that will select the all of the open actions with the earliest date (in the example that would be 17/01/2014) and move them to a separate sheet.

(For now I plan to date the actions by Fridays (eg 17 jan, 24 Jan, 31 Jan etc) but if that doesn't work I might look at changing to a date range of up to 5 days prior to "today" - but hopefully by then I'll know how to edit the code!!)

Thanks in advance for your helpactions register example.jpg

Hi catteeya

Thanks for the picture, I would have preferred an actual spreadsheet (i really hate transcribing test data into spreadsheet and avoid it if I can)

I will take a closer look at the picture while I am waiting and start to formulate a solution, stand by for more questions to validate any assumptions I am making in the code
Jmac1947

Catteya,
1. are you looking to generate an "actions needed on next Friday" list or a list of ALL open actions with a due date no greater than "next Friday"?
2. if you are going to execute the macro "manually" would it be OK to simply "ask for the end date" via a dialogue box?, that would solve any number of date related "gotchas" in excel
Jmac

catteeya-1.xlsm

Catteeya,

attached spreadsheet contains a macro that I think should either solve your problem or at least get you well on the way. Please read my assumptions in the macro code to understand what the macro is doing
There is probably only one key issue to be aware of. Col K in the Action Register tab MUST be formatted as a text column for this macro to be reliable (I have so many problems with date formats and date actions in excel that I simply avoid them whenever I can) and should always be in the format of dd/mm/yyyy

Hope this assists

Jmac

If this solution resolves you issue then please mark the thread "Solved" using the thread tools and if you are happy with the solution suggested then please consider clicking on the "*Add Reputation" button in the lower left of this post

Catteeya,

attached spreadsheet contains macro that I think should either solve your problem or at least get you well on the way.
There is probably only one key issue to be aware of. Col K in the Action Register tab MUST be formatted as a text column for this macro to be reliable (I have so many problems with date formats and date actions in excel that I simply avoid them whenever I can) and should always be in the format of dd/mm/yyyy

Hope this assists

Jmac

If this solution resolves you issue then please mark the tread "Solved" using the thread tools and if you are happy with the solution suggested then please consider clicking on the "*Add Reputation" button in the lower left of this post

Thanks, worked great, and sorry about the picture, couldn't figure out how to attach the file - you can see why I need help!! Will work on that for next time!!!