Macro Assistance Needed for Scheduling

I would like a macro that would find the current date on a large on-going schedule and create a daily schedule on the next sheet. More specifically the jobs that are on today's date under the extractions section would go into one row without spaces in the extractions table on sheet 2, the jobs that are on today's date under the LCMS section would go into one row without spaces in the LCMS table on sheet 2, and the jobs that are on today's date under the GCMS section would go into one row without spaces in the GCMS table on sheet 2. I was helped on a smaller version but my skills are very weak with VBA and I was not sure how to adapt it to the current schedule. Attached is an example, Sheet 2 shows the format that it should be in. Ignore the instrument and operator rows as those will be assigned daily. Thanks for any help.

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

Thanks rylo,

Is it possible to create a way to select the date that you would want to import? This would be helpful to see what ran yesterday and to schedule for the runs for tomorrow.

thanks again,

Jay

Jay

Very possible. How would you like to be able to nominate a date? Entry into a cell? Request from the macro when run? Do you want to have things changed so that the macro is run on demand, rather than when it opens? Or have it run on opening, but be able to manually call on demand?

rylo

Hey rylo,

being able to run on opening and then being able to manually call on the date would be useful.

Thanks,

Jay

Hi

Run on opening will work as it stands, and being able to call means creating a button that will just call the workbooks_open macro. But you don't specify how you want to nominate the date. Do you want the auto open to run the standard "today" run, or do you want it to request data from the user? If from the user, do you want it to be able to nominate "today" or always have to enter a date?

rylo

The auto open should be "today". In order to nominate the date I think that it should be, the user would enter in the date that they want to view and then have a button to press in order to retrieve the information from that date. Does that make sense? Thanks for your help rylo.

Jay

Hi

Where would they enter the date?

rylo

I have attached a sample spreadsheet. On sheet 2 you will see the buttons. Let me know if this would be the best way or do you have another idea.

Thanks,

Jay

Hi

Easier way.

If sheet2!C18 is blank, then it will bring in today's data. If you have a date in there, then it will bring back the data for the nominated date. So you really only need to have one button to manually call the macro, and it depends on what is in C18.

Be careful though. If you save the file with a date in C18, then it will bring back data for that date next time it opens. You could do a workbook_ beforeclose to make sure C18 is blank. Or maybe add an extra command to the macro to blank out C18 whenever it is run.

Please Login or Register  to view this content.

rylo

It is not importing anything? what am I doing wrong?

Ok, I got it to run. But changing the date in c18 does not import work from that date.

Jay

Hi

Oops, try this.

Please Login or Register  to view this content.

rylo

I have this macro that grabs the information from a schedule for the current date or which ever date that is clicked on the calender. I was wondering how to keep the format of just the text (whether bold or a different color that is imported from the schedule. Attached the spreadsheet with the macro.

Thanks,

Jay