consolidating multiple calendars into one

[camri6]

This seems to be way more difficult than I anticipated, I just need a little help with consolidating. Tried the consolidate feature, however not sure that it is what I am looking for.

I am basically trying to condense several calendars ( one calendar per department in a company) into one Calendar..Each calendar has its own tab and looks exactly the same. (tab names: Clubhouse, spa, and final calendar... possibly more departments added later) Hard to explaing so I will attach the file. All I would like to do is combine all calendars into the "final calendar" and in time order if possible... example below

{Clubhouse Tab} A3 will have the time (10 am) , B3 will have the item "Meeting"
{Clubhouse Tab} A4 will have the time (5 pm) , B4 will have the item "Dinner"

{Spa Tab} A3 will have the time (12 pm) , B3 will have the item "Lunch"

{Final Calandar} A3 will have the time (10 am) , B3 will have the item "Meeting" - from Clubhouse Tab
{Final Calandar} A4 will have the time (12 pm) , B4 will have the item "Lunch" - from Spa Tab
{Final Calandar} A5 will have the time (5 Pm) , B5 will have the item "Dinner" - from Clubhouse Tab

If anyone can help with this I would appreciate it, and I can get a god night's sleep!

[watersev]

hi camri6, option, press "Run" button or run code "test"

[camri6]

Thank you so Much Watersev, (amazing code)

It works perfectly except I am getting cut off at row 35. I took a look at the code, and I am not good at all at reading a lot of It, but I did see 35 in the code a couple of places, but I am not sure just changing those instances of 35 to 37 will do the trick.
My only other question is if I add more department calendars?

Thank you for your help

[watersev]

hi camri6, I have checked the file, it was my typo, I have corrected it.

The code checks every visible sheet for a calendar on the sheet except for Final Calendar so it should be fine. The only possible issue that may arise is that planned events for one particular day may exceed available rows for that day. As soon as this does not happen it should be fine.

[camri6]

Hey Watersev,

This is coming along nicely... Two quick thoughts, It looks like that when I started entering more data that any given week will depend on what's above it and what's under it, I attached it to get your thoughts.. Also the time sorting in week 1 looks to be off. My apologies again, you have made this this spreadsheet amazing and more than I thought possible so thank you for all your help on this

[watersev]

sorting issue is corrected.

The current calendar layout has limitation set above. It's up to you to decide if you can live with it.

[camri6]

No No this is absolutely perfect! Thanks Again for this...... Last thing and I am Done, Promise!.. Conditional Formatting text font..what would be the easiest way to bring the font color over to the final calendar? (each department will have it's own font color)

Thanks again Watersev!

[watersev]

please check attachment

[camri6]

Watersev,

Your help hopefully one last time!

I added other calendars, everything is fine except i get an error I think because of the font script you were kind enough to add for me. Other than that I think its fantastic....Is there a way to match the font to the tab color I created or close? (The time can be included in this font color instead of using black(final calendar)
)
With the new tabs I added, it's bringing over the name of the tab as well as the item for that time?

[camri6]

Also if you know any good sites for learning VBA, I would gretaly appreciate it. I think it's awesome what you are able to do!

[watersev]

please check attachment.

Re: learning VBA: http://www.excelforum.com/excel-prog...materials.html

[camri6]

Calendar36.xlsmHey Watersev,

BTW this calendar has really helped!! So I wanted to say thanks one more time.

It looks like I ran out of space for this november calendar, is it possible I can add another row of boxes without completely destroying the code?

[watersev]

add the row of boxes you want and post a file

[camri6]

Here it is!

[watersev]

please check attachment

[camri6]

Hi watersev! I was checking the "golf tab" and for some reason it's not pulling to the final calendar. I looked through the code and everything looks fine. Just curious if you had a second to see if you can see anything.. As far as I know it's just the "golf tab"

[watersev]

the code checks eligibility of the sheet for processing by checking range A2 that should contain text "Sunday". Golf tab range A2 is empty so the code omits it.