Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-26-2015, 06:15 PM
andrew22xc andrew22xc is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2007
Novice
Schedule Converging
 
Join Date: May 2015
Posts: 4
andrew22xc is on a distinguished road
Default 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!
Attached Files
File Type: xlsx TRNG SCHEDULE FINAL FORMAT.xlsx (13.1 KB, 11 views)
Reply With Quote
  #2  
Old 05-27-2015, 11:30 AM
NoSparks NoSparks is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Attached Files
File Type: xlsm TRNG SCHEDULE FINAL FORMAT.xlsm (24.7 KB, 11 views)
Reply With Quote
  #3  
Old 05-27-2015, 06:50 PM
andrew22xc andrew22xc is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2007
Novice
Schedule Converging
 
Join Date: May 2015
Posts: 4
andrew22xc is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-28-2015, 07:28 AM
NoSparks NoSparks is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Attached Files
File Type: xlsm TRNG SCHEDULE FINAL FORMAT_v2.xlsm (29.0 KB, 12 views)
Reply With Quote
  #5  
Old 06-01-2015, 05:00 PM
andrew22xc andrew22xc is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2007
Novice
Schedule Converging
 
Join Date: May 2015
Posts: 4
andrew22xc is on a distinguished road
Default 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!
Reply With Quote
  #6  
Old 06-01-2015, 06:49 PM
NoSparks NoSparks is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Reply With Quote
  #7  
Old 06-01-2015, 08:46 PM
NoSparks NoSparks is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #8  
Old 06-01-2015, 10:08 PM
andrew22xc andrew22xc is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2007
Novice
Schedule Converging
 
Join Date: May 2015
Posts: 4
andrew22xc is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 06-01-2015, 10:44 PM
NoSparks NoSparks is offline Schedule Converging Windows 7 64bit Schedule Converging Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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:
This has saved my company a lot of money on more expensive alternatives.
Does that mean we get a raise?
Reply With Quote
Reply

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
Schedule Converging showing fabrication items in schedule ketanco Project 4 06-24-2014 12:18 PM
Schedule Converging 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft