Create worksheets for weeks between and including first and last Mon of two month period

[L plates]

Hi All,

I'm nearing the end of creating a staff schedule template that will allow users to create a bi-monthly staff scheduling workbook that is broken down into a separate worksheet for each week within a two month period. I have created a user form that is called when the 'Schedule Master Template' (attached) is opened. The user form has two combo boxes - one to select a year for the schedule and the second to select the pair of months the schedule is for - and a command button to run the macro that creates the workbook.

It is the macro that creates the schedule from the user form command button click that I need assistance with. I'll try to describe the requirements below.

Userform ComboBox1 selections are: Please Select, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026

Userform ComboBox2 selections are: Please Select, January/February, March/April, May/June, July/August, September/October, November/December

Userform CommandButton1 is to create the schedule.

Using the selections for year and month pairings in the two combo boxes as references, I am hoping to populate cell E2 on sheet 3 with the first Monday of the month and cells H2, K2, N2, T2 and W2 with the next six consecutive days. For example, (as per the TEST attachment) for selections 2015 and March/April, E2 will be 2nd March (first Monday of the month) in the format MONDAY 02 MAR or Monday 02 Mar, and and cells H2, K2, N2, T2 and W2 will be populated with the next 6 days. The sheet should then be named with the first and last day on the sheet + the month. Format for this sheet first sheet should show '02-08Mar'.

The macro should then create further sheets for the next number of weeks until the last week containing the last Monday of the second month has been reached, naming each day within each worksheet at E2, H2, K2, N2, T2 and W2 and naming the sheet as it progresses.

The 'TEST - 02_Schedule_Mar_Apr_2015' attachment has what the finished schedule would look like. Please ignore errors within the workbook as this is a work in progress and could also be due to me removing sensitive data.

The biggest hurdle I have is having the macro recognise the first and last Monday of a two month set when creating new worksheets. If someone could assist with code to create the necessary worksheets from the userform data selections, I could probably sort out the naming of the days and worksheets myself.

Any help would be greatly appreciated!

Regards,

[skywriter]

Hi Dave,

If you are still watching this thread I wrote you some code to get you started.

The first is some code for the button that will call a couple of functions to get you the first and last Mondays based on what your users pick.

The second is the functions themselves.

Right click the command button, then view code and paste this code there.

Please Login or Register  to view this content.

Go to insert, then choose module and paste this code there.

Please Login or Register  to view this content.

[skywriter]

Here's what I have so far.
See if you like it.

[L plates]

Hi skywriter,

Firstly, many, many thanks for your assistance with this and secondly, sorry for the late reply! I've never used functions in VBA before but I see the direction you've headed with them even if I don't fully understand at this stage.

I tried opening the file you attached, selected a year and month set and once the command button was clicked received an error - Runtime error ‘32809’ Application-defined or object-defined error. The Copy Sheets Sub is quite simple but I just can't see why Sheet 3 was not recognised (if this is the cause). I did notice that there is an extra worksheet, 'Sheet1', already added which did not appear to serve a purpose but deleting this from the template didn't help resolve the error.

Do you know what might be happening with the runtime error? My profile says I use Excel 2013 which is when I'm at work but I occasionally use my personal laptop which runs 2010. Clutching at straws here but could this be a reason?

Thanks again for your start on this macro.

[skywriter]

My first question would be did you open the actual sheet and run this from the sheet I posted, or did you copy the code and put it into another sheet?

When the error comes up one of the choices you would have is a button labeled debug, if you push that button it will take you to the code with a line highlighted in yellow. I would like to know what that line is.

[L plates]

Hi skywriter, yes I did open your attachment and this is where I encountered the error:

Please Login or Register  to view this content.

I'm ok at debugging but I'm still a bit baffled as to why this line raised an error. This aside I have now applied your code to my own sheet and saw how you have pointed me in the right direction with the Msgboxes. I then added the CopySheets Sub and also the Private Sub CommandButton1_Click() as per your attachment, reopened the new workbook and can say that you have absolutely nailed what I was trying to achieve. Thank you very much! I have begun to add further functionality to the macro to complete the template such as formatting headings as per below.

Please Login or Register  to view this content.

Even though I missed it first time round, thanks for demonstrating the method the way you did. It was extremely helpful!

Regards,

[skywriter]

Hi skywriter, yes I did open your attachment and this is where I encountered the error:

Please Login or Register  to view this content.

I'm ok at debugging but I'm still a bit baffled as to why this line raised an error. This aside I have now applied your code to my own sheet and saw how you have pointed me in the right direction with the Msgboxes. I then added the CopySheets Sub and also the Private Sub CommandButton1_Click() as per your attachment, reopened the new workbook and can say that you have absolutely nailed what I was trying to achieve. Thank you very much! I have begun to add further functionality to the macro to complete the template such as formatting headings as per below.

Please Login or Register  to view this content.

Even though I missed it first time round, thanks for demonstrating the method the way you did. It was extremely helpful!

Regards,

I'm glad it worked out and thanks for the reputation points.
Good luck!!! My pleasure.