linking calendar to excel worksheet

raghavfastest · #1 06-27-2009, 02:06 AM

Hi guys,

need your help again.

I was trying to develop an excel based calendar schdule for the material dispatches being done from our warehouse. have been able to cook up something using the vba, ingeniously from resources over net and some help from here, but it has not given the desired results. actually the code is not doing anything.

Am attaching a sample workbook, c if you guys can be of help as always you are..

Simon Lloyd · #2 06-27-2009, 05:21 AM

You need to explain what it is you want to see happen and under what conditions.

shg · #3 06-27-2009, 09:22 AM

raghavfastest,

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

raghavfastest · #4 06-29-2009, 05:43 AM

hi,

Ok. understand your concern. let me try to give a detailed descriprtion.

Im looking after a warehouse which stocks goods and materials after production at our plant. These stored goods are to be shipped to the designated customers, after receipt of the dispatch plan from the commercial team or as per the dispatch schedule decided by the customer.

For managing and keep a track of the dispatch activity have tried to create a VBA enabled excel sheet (earlier also we were doing it through excel only but most of the data was entered manually and also keeping a track of dispatches executed and dispatches pending was done manually). The whole idea is about to know at any given time or day what deliveries have been executed and what is pending to be done or is delayed, etc. A concise report summary kind of thing for efficient planning etc and to avoid manual errors.

In the workbook attached; there are two sheets - the schedule sheet (which gives the description of activities to be done for that particular day/date alongwith the status of the activity) and the data sheet (which stores the entire data and from where the information will be populated into the schedule sheet)

The columns in the data sheet are self expanatory and so is the schedule sheet. what i want the schdule sheet to do is that upon selecting a date from the calendar it should show the date selected in 'Schedule FOR' row and then scan the data sheet for data on or before the selected date and populate the data in the schdule sheet from row # 14 under the selected column headings along with their current status based on the color legend in the schedule sheet. The data in the data sheet will be colored beforehand on the basis of the current status of the activity. These column headings have been selected from the data sheet itself, so all it is about the arranging the data in the relevant columns in the schedule sheet with the

shg · #5 06-29-2009, 04:07 PM

This title is no better. Please try again to produce a concise, descriptive thread title that explains your problem.

raghavfastest · #6 06-30-2009, 05:16 AM

hi

Have uploaded the fresh sheet. have inserted comments in the vba code to try to explain what the code is supposed to do and what will be desired results of it. Hope now it will easier for you guys to go through it and suggest how to make it work. till now it's just piece code that does not work.

raghavfastest · #7 07-02-2009, 03:59 AM

hi,

Guess the topic was tad too boring for you guys to pick your brains to no suggestion at all. any way have been working on the file and have been able to make the code work somewhat. the details have started to reflect though not as i wanted to them to be.

a few errors have been thrown by the code:

1) 1004:method 'range of object'_worksheet failed

also i want the schedule sheet to consider date of schdule from the data sheet only from column B in the data sheet. currently it is picking date from column C too which should be excluded or not taken into consideration while scanning the data.

Gurus plz help this time..

**royUK** · #8 07-02-2009, 04:35 AM

It might help if you posted to the correct Forum

Moved to Programming

raghavfastest · #9 07-02-2009, 09:01 AM

have tried a lot but unable to come up with a solution for the error: 1004:method 'range of object'_worksheet failed

also the code keeps on looping through entire sheet; whereas it should be looking only in column b for dates and not the entire sheet.

guys plz hlp..

raghavfastest · #10 07-02-2009, 12:41 PM

the error still persist. guys plz help

**royUK** · #11 07-02-2009, 12:52 PM

Your code has error handlers in, these are a good idea, but should be applied after the code is finished 7 tested, if not how do you know what error is occurring?

The code isn't working because you call your calendar - CalSchedule, however you haven't renamed it, it is still Calendar1

**royUK** · #12 07-02-2009, 01:02 PM

It's hard tracking through your code, there's no notes to help.

I would simply use the value from the Calendar to filter & copy the data from the list to the schedule sheet

raghavfastest · #13 07-03-2009, 01:08 AM

hi roy,

thanks for responding and taking the pains to going through the code. did figured it out that the calendar was not named correctly so had done that but still the error 1004 continues.

also had written notes in the code for easy understanding. that is why had uploaded the fresh sheet. am attaching it here again.

plz help to sort the issue

**royUK** · #14 07-03-2009, 02:28 AM

If you had figured out the naming, why didn't you let us know immediately and save time?

Also, try my AutoFilter suggestion

raghavfastest · #15 07-13-2009, 08:37 AM

hi roy,

has uploaded the corrected file immediately. sorry to not having mentioned in the message. also can you please tell me how to use the auto filter function. dont know much about it.

plz.

Welcome to the Excel Forum

Forum Statistics