#1
|
|||
|
|||
Schedule Converging
Coding Gods,
I am currently trying to solve a problem that my upper management has given me. They have a training schedule that has different sheets for different departments in the company. Once each department has uploaded to the workbook, they want all the individual departments to merge into one master sheet with everyone's training events. In the example posted below I would need the IT and LOG sheets to populate to the master sheet (in the correct time/date order and in the same format). Keep in mind, the number of events for a day is dynamic. One day could have four events and the next could have only one. I am no VBA master by any means and am struggling to find anyone with the same requirements. Any help with the example would really help me. I know, there are multiple other resources that would be much easier and feature rich but this is the way they want it done. Once again, thanks for your help! |
#2
|
|||
|
|||
You don't show what the desired results on the Master should be for the IT and LOG sheets you've posted.
I've deleted everything on the Master except for the first line. Alt + F8 brings up the macro dialogue, run the macro. Hope I have commented the macro enough for you to follow and alter as necessary. Depending on what is actually wanted you may need to alter or add to the sorting and/or filtering. A couple comments to assist with VBA: Time and dates.... use time and dates. Not text. Merged cells.... avoid them like the plague. |
#3
|
|||
|
|||
Thank you very much for the response. It is doing exactly what it needs to except that the individual days events need to be in time order on the master (regardless of what unit/department is doing it). In the example, events go 0800, 1000, 0830, 1030. They should be 0800, 0830, 1000, 1030. Also, in your script, you removed the merged date cells. Is there any way to remerge them again in the master?
Thanks again for your help. I would have never been able to get this far in the time allotted for completion. |
#4
|
|||
|
|||
After always swearing at merged cells, it's definitely strange to be merging them programmatically.
Hopefully this meets your requirements, if so, please mark the thread as solved. |
#5
|
|||
|
|||
Help
The script is cutting out the "B" Events, on the final day, in the master. I'm trying to find where this is happening but can't isolate it. Any help is appreciated! So close!
|
#6
|
|||
|
|||
YUP..... it's those friggen merged cells !!!
In the first macro, in the copy data to scrathpad portion, in the line lr = ws.Cells(Rows.Count, 1).End(xlUp).Row Excel was not getting the last row of column1 (A), it's getting the top of the merged area. I change the 1 to 2 in order to use column B to find the last row and now it works for one of your IT sample sheet and not the LOG sheet, complains about can't change part of a merged cell, and that's even after adding 10 to lr. Not sure what's required but I'm looking into it. If another member has a solution for this, PLEASE help us out. Thanks. |
#7
|
|||
|
|||
OK think I got it, was actually 2 problems dealing with the merged cells.
Replace the original 'copy to scratchpad section of the first macro with this and it should fix things up. Code:
'copy to scratchpad For Each ws In Worksheets If ws.Name <> "Master" And ws.Name <> "ScratchPad" Then pasterow = ws2.Cells(Rows.Count, 2).End(xlUp).Row + 1 If pasterow = 2 Then pasterow = 1 With ws 'copy data to scratchpad lr = ws.Cells(Rows.Count, 2).End(xlUp).Row .Range("A2:I" & lr).Copy ws2.Range("A" & pasterow) ws2.Columns.AutoFit End With End If Next ws |
#8
|
|||
|
|||
Worked!!! Thanks a ton! There is just one last thing that employees are running into. I have around 5 worksheets pulling into one master so it gets pretty long. When someone updates a sheet they have to back and delete the entire master (minus the header). It would be much more convenient to the user if it auto deleted the master (minus the header) and then repopulated it every time the macro was run. After that, I haven't received any complaints. Can't thank you enough for your help. This has saved my company a lot of money on more expensive alternatives.
|
#9
|
|||
|
|||
To clear Master: At the beginning of the first macro, between
Application.ScreenUpdating = False and 'create scratchpad worksheet add this Code:
With Sheets("Master") .Range(.Range("A1"), .UsedRange).Offset(1, 0).Delete Shift:=xlToLeft End With Quote:
|
Tags |
excel 2007, vba code |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Daily Loan Tracking schedule | pranjal | Excel | 1 | 12-04-2014 11:57 AM |
what to put in to schedule for dealing with overall finish date | ketanco | Project | 7 | 10-30-2014 02:25 PM |
showing fabrication items in schedule | ketanco | Project | 4 | 06-24-2014 12:18 PM |
Getting Error when I schedule a meeting in outlook | ms3433 | Outlook | 1 | 09-05-2013 11:03 AM |
Powerpoint project schedule | L J Keane | Project | 0 | 09-14-2010 01:24 AM |