Search Dir for Workbook and worksheet and add either as required

[Mike Ryan]

Hello and I'm pretty new to VBA so please bear with me

I have daily worksheets (mon - fri) in a weekly workbook (1 - 52) for a year. What I want to do is use a single worksheet in a single workbook that I can use as an input model, where once I have finished updating I can, on click of a button, run a macro that will return a message box or userform displaying the last workbook saved and the last worksheet added to that workbook. For Example: Workbook 3 / Worksheet Wed. This lets me know that if I continue, the model worksheet will be copied/saved to an added worksheet named Thu in Workbook 3. Then the model will clear leaving a message box stating Week 3, Thursday has been saved. Please let me know if I'm barking up the wrong tree.

[Leith Ross]

Hello Mike Ryan,

Your are the right track. A VBA UserForm makes organizing and controlling user input much easier. But, it requires more programming than entering data on a worksheet. To determine which approach would be best, you would need to post a sample of your data layout, both input and output.

If you can not post it here, you can upload your sample to a public file sharing service and post the link in this forum. If can upload your workbook, that would be even better. If you have sensitive, personal, or confidential information in your workbook, please redact it before you upload.

[Mike Ryan]

The data is in the form of a heijunka or scheduling tool...so basically a table of data moving across various time slots as columns and moving down across various names...intersecting cells will have various duty requirements.

[Leith Ross]

Hello Mike,

It has been 12 years since I did anything with Sigma Six. So, I am more than a little rusty.

If you post a picture of how your data is organized it would be a big help.