Multiple Entry Locations To List For Holiday Tracker

**FoxDangerMolder** · 04-19-2016, 10:19 AM

Hi all,

I have been working on this issue and have not been able to find a simple solution to it.

What I'm doing is making an excel sheet to track out holidays, overtime, shift cover, and sickness all in one spreadsheet. All of these are being put on a shared company outlook calendar so everyone is kept in the loop. I have managed to make it so i can put all of these in one list and click a button to add them to the calendar but where I'm having problems is listing those entries into individual sheets so they can be put on a tracker(Sheet1). I need each employee to have a sheet that lists each day of holiday and cover to pull on to this tracker and the date it happens. I have information in column B and C that i need and the date in column F (on sheet "list").

Ex. J Smith is on Holiday(Column B) and J Jones is Covering him (Column C) both happening on Date 01/01/2016 (Column F)
On J Smith's Sheet (JS) I need the following Entry:
01/01/2016 HOL

On J Jones Sheet (JJ) I need the following Entry:
01/01/2016 CVR

I have typed these entries in (as an example for how i want Sheet1 to populate) but i'd like these entries to automatically generate from the List sheet. I've attached a simplified version of my sheet.

Boiled down, I want to make one entry on the spreadsheet and fill out all the other forms. This is currently taking a lot of my time because of the number of employees so I'm trying to make it quick and easy(and accurate).

Sorry if this is long winded - I'm sure there is a simple solution I am not seeing but I'm loosing my mind trying to find it.

Chris

**jaslake** · 04-19-2016, 04:10 PM

Hi FoxDangerMolder

Welcome to the Forum!!!

The Code in the attached will do this...

listing those entries into individual sheets so they can be put on a tracker(Sheet1).

Please note the following:

I've changed the Sheet Names to correspond to the name of the specific individual.
Your Button now calls Sub Create_Individual_Sheets()
Included in Sub Create_Individual_Sheets() is a line of Code that calls Sub CreateOutlookApptz()

Please further note that in Sub Create_Individual_Sheets(), Sheet Lists is being filtered on Column K for Blanks. This is to prevent previously extracted items being pulled again.

You'll need to do something in Sub CreateOutlookApptz() to see if the appointment already exists or you'll get multiple occurrences of Appointments created.

Please Login or Register  to view this content.

**FoxDangerMolder** · 06-17-2016, 01:39 AM

Hi jaslake!

Sorry for taking so long to come back on this one, i was pulled off this for a while to work on something else now i'm back at this

At first with a little tinkering this was working beautifully, but as i was putting the final touches on the sheet something has gone wrong and I cant for the life of me figure it out.

When i run the macro I get Run-time error '9': Subscript out of range. When i click debug it highlights Ln 28 Col 40 - Set ws1 = Sheets(wsName)

Any ideas on what has happened?

**jaslake** · 06-17-2016, 11:05 AM

Hi FoxDangerMolder

Subscript out of range would indicate the Code cannot find Sheets(wsName).

**FoxDangerMolder** · 06-20-2016, 05:50 AM

Gotcha... What do i need to do to fix this?

**jaslake** · 06-20-2016, 01:38 PM

Hi FoxDangerMolder

Without seeing your workbook, best I can suggest is check the spelling of the worksheet when the Code errors.

**FoxDangerMolder** · 06-21-2016, 07:58 AM

Almost there now lol. I have the macro putting information on everyone's individual sheets but now the calendar is not updating. I'm not receiving any errors its just not doing anything on the calendar and its not marking it as imported either. This is the code I'm using that you gave me but I've added in .AllDayEvent = True. Sorry for being such a pest with this one, you've been such a massive help with all of this!

Please Login or Register  to view this content.

**jaslake** · 06-21-2016, 11:14 AM

Hi FoxDangerMolder

When posting Code please wrap the Code in Code Tags...it's a Forum requirement.

Change the indicated Line of Code...

Please Login or Register  to view this content.

**FoxDangerMolder** · 06-22-2016, 02:50 AM

Hi jaslake

The calendar is working! But now for some reason its not sending all the information to individual sheets. Seems like there is a point its done up to and it wont go any further. I'm sorry this is turning out to be such a pain pal. :/

**jaslake** · 06-22-2016, 10:22 AM

Hi FoxDangerMolder

It's not possible for me to debug your Code without seeing the File with Code embeded...

**FoxDangerMolder** · 06-22-2016, 12:03 PM

Hi Jaslake

I have cleared out the private data but most everything else is intact.

**jaslake** · 06-22-2016, 01:34 PM

Hi FoxDangerMolder

When you say this are you referring to M Casson for example?

But now for some reason its not sending all the information to individual sheets

**FoxDangerMolder** · 06-23-2016, 03:24 AM

Hi jaslake,

I'm not sure if he was one, but if you look at D Tucker HOL for June 24th (one of the most recent entries on List) , its imported but not added to D Tucker's sheet or to Sheet1. I can't figure out how to stop the repeated entries each time Import either :/

**jaslake** · 06-23-2016, 03:35 PM

Hi FoxDangerMolder

There are a couple of issues with your attachment:

Your Button 1 was assigned to run Public Sub CreateOutlookApptz() and it should have been assigned to run Sub Create_Individual_Sheets() (see Post #2).
In the case of M Casson the Data entry is not "normalized".

PHP Code:

"M Casson HOL"

is not the same as

PHP Code:

"M Casson HOL"

...there's an extra space between Casson and HOL such that this line of Code does not find anything.

Please Login or Register  to view this content.

I've modified the Public Sub CreateOutlookApptz() code such that it no longer Loops through all the records in List. The Code now filters only those records that are blank in Column K (does not contain "Imported". This should help mitigate this issue.

I can't figure out how to stop the repeated entries each time Import

**FoxDangerMolder** · 07-05-2016, 11:49 AM

Hi Jaslake,

Thank you so very much for all of your help! This is working perfectly and is saving me a lot of time

**jaslake** · 07-05-2016, 05:40 PM

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

Multiple Entry Locations To List For Holiday Tracker

LinkBack

Thread Tools

Rate This Thread

Display

Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Re: Multiple Entry Locations To List For Holiday Tracker

Thread Information

Users Browsing this Thread

Similar Threads

Holiday Tracker - Summary Sheet - Help Needed.

[SOLVED] Holiday Tracker

Holiday planner/tracker

Holiday Leave Tracker Help

Holiday Tracker

Employee Holiday Tracker

holiday tracker

Bookmarks

Bookmarks

Posting Permissions