I have had to create a holiday tracker as the one currently used keeps crashing. The excel workbook I set up has a front sheet with 20 very hidden worksheets. The plan is when a name is entered into a range on the front sheet (team summary) a sheet will unhide and be named with the initials of the name entered into the cell.
Each sheet will be fed brief information from team summary page such as name, holiday entitlement and team name.
In the worksheet itself a lot of the cells will be hidden but there will be a main table for inputting data for each day. There are a couple of options that I would like to attempt using VBA:
Create a multipage or multi-tab userform with the following options:
Page 1 - Import another sheet - If someone moves team how could I import their information over?
Page 2 - Update Info - Have a text box or drop down box showing the absence types with listfill from range (N13:N24). When a particular option is selected with the number of days it appears on the main table with the colour code & number of day. Refer Jan 14 & 15 on main table to see what I mean. Also when the main table is updated for a holiday, how would this then feed through to Holiday Record Form to the left of the main table? I would also like to do this for sickness & Flex, that both have separate tables.
Page 3 - Clear Info - To clear info previously input, in the event of someone changing a holiday etc when cleared this would also remove from the tables surrounding the holiday, flex or sickness tables
Page 4 - At the start of the year set rest days for the rest of the year. Have an option showing from & to and this updates the main table. Can a small tick boxes be added for each day of the week to select the rest day(normally a Sat/Sun but if someone is part-time this would come in handy)
Also from rows AW29-BA51 for each alternate line, I need to sum hols, sick, flex, training & bhols. If a cell shows as H1, how can I do a sum for that particular row (L29:AV29)
Each sheet will also have print options as per the first sheet JB, is there a quick way to copy and paste these with a macro to print each area?
I attach a copy of the sheet for you to view and this might make it clearer where everything fits, I can get so far but I just don't have the know how with code to do all of this, that's where you guys come in!
Any queries let me know, thanks
Coxy31
Start Free Trial
