Enter Shift In Schedule For Printing

  • Hi long time reader, first time poster! I hope that my post title was acceptable.

    I have a staff scheduling workbook that my team members use to create weekly employee schedules. They would like to enter the shift info into a table laid out in the manner it will be printed and posted. The attached example #1 in the file will illustrate.

    I need to have the schedule info the team member enters be copied onto a different worksheet in the workbook in a conventional list format so that I can build a pivot table from it. I am very comfortable with the pivot table use once I get the data into the correct form, see example #2 in the file.

    Your help is greatly appreciated as I know the correct solution is to bag the whole mess and learn access, but our busy season is kicking in and I won't have time until june! I consider myself very comforatble with excel in general but have never ventured into VBA. I am ready to cautiously go into that good night if needed (with guidance)!

    Thanks Again, your time is very appreciated!

  • Re: Enter Data In Table For Printing, Copy Data Into List Form

    G'day Cameron,

    Welcome to Ozgrid...

    Using macros can save time and energy but it can also be more complex that you might want, especially if you are not familiar with them.

    From the example you have given (and I am assuming the number of team members is a few more than what your are showing, and that your would be scheduling for a week at a time), may I suggest creating a template for the schedule (like your example 1) on sheet1, then on sheet2, create the format you want by using formulas. (see attached)

    This then can include other information such as Joe is not on and is seen as absent

    It also means it is a simple manual cut and paste or a simple macro to automate the process of cutting and pasting for use in your pivot table or sort it or print it or what ever you would like...



  • Re: Enter Data In Table For Printing, Copy Data Into List Form

    Thanks for the suggestions!

    I am trying to go the Macro route to make the schedule tool as easy as possible for the users. I put the module into the schedule workbook and then tried to edit it to work with the correct worksheet names and ranges.

    I am now getting a runtime error '424'

    Here is the original code (just a portion of it):

    and here is my adaption:
    Dim lrow As Long, r As Long, wks As Long, rws As Long, i As Long
    Dim rng As Range

    With Culinary_Schedule
    rws = .Range("B16").End(xlDown).Row [COLOR="SeaGreen"] this is where the error occurs[/COLOR]
    wks = (.Range("B16").CurrentRegion.Columns.Count - 1) / 4

    Any thoughts? I didn't want you to "write my code for me" but I'm stumped! Any suggestions on how I can get the original macro to work in the new location or do you need more info on the worksheet?


  • Re: Enter Data In Table For Printing, Copy Data Into List Form

    It's easier if you post your file as is to avoid these difficulties. If Culinary_Schedule is the name of your worksheet you need to use

    With Worksheets("Culinary_Schedule")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!