Make checklist from closed WB's and then save updated C'List data back to source WB

Hi all, I am trying to learn VBA (mostly through examples on forum searches & VBA for dummies) and I think my head is about to explode!

I have tried many examples of similar questions/problems with modifications but I can't seem to get anything to do anything!

I am hoping someone can help me with what is probably a reasonably simple solution.

I am using Excel 2010.

I have a "Job Checklist" where all my job data is stored. Job #, Address, Details etc, and Scheduled completion date.

There are many job checklist work books (and growing in number) stored in a single folder and I want to create a "To-Do List" workbook in the same folder that will check for upcoming jobs when it is opened or a button to "refresh" the sheet (without opening all the Job workbooks).

The "Job Checklist" will have file names like Job Checklist 1500, Job Checklist 1550, 1600, etc.

I would like the "To-Do List" to show me jobs that are coming up based on how many weeks to go before scheduled completion. (ie: 6 weeks to go, 5, 4, 3, 2, 1) shown in Columns C, D, E F, G based on the current days date (cellX2 Job C'List).

There are probably 60 or 70 jobs at any one time that would be 6 weeks or sooner to scheduled completion date.

So the To-Do List doesn't become endless I would also like to add a check box or a tick in the "complete" cell (Column H) that updates "Job C'List X" and saves updated C'List Data back to "Job C'List X" in cells in column V that corresponds to the job, so the job is marked as done and doesn't appear in the "To-Do List" again. (Maybe a button function of some sort?)

I have attached sample work books so hopefully someone can give me some pointers on where to start.

Regards,
Martin

Hi again, no one seemed to reply, probably because I was a complete noob and not having a proper go at the problem myself.

I have tried several methods on this and other forums and have found that Ron De Bruins (base) code does what is needed with the necessary tweaks.
The only thing now I can't get to work the way I want, is how the copy/paste data populates the cells in the new workbook.

It seems to have everything to do with the line of code:

BaseWks.Columns.AutoFit

I have different column widths on the source Workbooks for the data. When the macro pastes the data in the new workbook, it keeps the new, blank workbook standard column widths and makes the rows massive.

Any suggestions on what the work-around for this might be?

Cheers,

Martin

Anybody with any suggestions?

Hi all. Couldn't sit around and wait for suggestions so I did it myself (well Sort of!)
Firstly a big thanks to Ron De Bruin for making the foundations of this code freely available.

To some of the "Guns" on this forum the code probably will give them a headache.
Ron's part of the code, of course, is quite neat and efficient.
My additions have mostly been recorded with the Macro Recorder and modified to suit from there.
Anybody reading this and looking for answers.... The Macro Recorder can be you best friend when learning/beginning macro programming.

Anyways, Here's what I finally came up with. Works like a charm (for what I need it to do).
I'm gonna mark this Thread as SOLVED (by me!!!)

(I have to paste it in parts because it's too big for 1 thread!)

3rd (and Messiest) part of the code :

Please Login or Register  to view this content.