📣 Is this even possible?!!! Convert one schedule layout to another

[deliddell]

Hello Everyone!

I am a stage manager for a live entertainment production. I have implemented a new scheduling system called Virtual Callboard and am hitting a few obstacles with trying to convert the old system to the new one.

When I create a draft schedule to send to managers to review before publishing it to the entire company, the managers have a current spreadsheet format that they prefer. Although Virtual Callboard does export the draft schedule to a csv, the managers are not thrilled with the layout and would like it to look more similar to what they are used to.

Is there any way to create a macro (or anything else) that would convert the Schedule Draft Export to the Schedule Target Layout? I've attached both examples. The schedule data is not the same in both but it gives you an idea of the layout.

I was wondering if it's possible to separate the schedule into sections according to date, title each section with the corresponding date, and clean up the date/time format.

Any help/suggestions/comments would be immensely appreciated!

Thanks... David

Also asked here Is this even possible?!! Convert one schedule to another

Convert from this...

...to this!

 

[tj4242]

With the data you provided in the "from this..." sheet, there is no way to determine the bolding, the italics, the coloring, etc. Also, the carps, R, LX are in the Notes column but don't display. And you don't do anything with the names in Users Called. But this should get you going. Make sure the "from this..." worksheet is named Draft Schedule Export.

Sub ConvertSchedule()

Dim newFormatLine As Double
Dim curentDate As String
Dim mergestring As String
Dim lastDate As Date

' Create a new worksheet for the schedule
Sheets.Add.Name = "New Format"

'Set a dummy date for first loop
lastDate = CDate("1/1/1900")

'Just some formatting
Range("C:C").WrapText = True
Range("C:C").ColumnWidth = 40

'New format starts on row 10
newFormatLine = 10

For i = 2 To Sheets("Draft Schedule Export").Range("A1").End(xlDown).Row                        'loop until out of data

    If Left(Sheets("Draft Schedule Export").Cells(i, 3).Value, 10) <> lastDate Then             'if the date changes start a new date section
        currentdate = Format(Sheets("Draft Schedule Export").Cells(i, 3).Value, "long date")    'save the current date
        Sheets("New Format").Cells(newFormatLine, 1).Value = currentdate                        'Put the date in the new format
        Sheets("New Format").Cells(newFormatLine, 1).Font.Bold = True                           'format it
        
        mergestring = "A" & newFormatLine & ":F" & newFormatLine                                'setup for cell merge
        Range(mergestring).Merge                                                                'merge them
        
        lastDate = Left(Sheets("Draft Schedule Export").Cells(i + 1, 3).Value, 10)              'Save the date
        mergestring = "A" & newFormatLine & ":F" & newFormatLine                                'setup string for handle border formatting.  Reusing the variable "mergestring"
        Range(mergestring).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous           'bottom border
        Range(mergestring).Borders(xlEdgeBottom).Weight = xlThick                               'make it thick
        
        newFormatLine = newFormatLine + 4                                                       'skip 3 lines before the details
    End If
    
    Do While Left(Sheets("Draft Schedule Export").Cells(i, 3).Value, 10) = lastDate             'while there are items for the current date
        currentdate = Format(Sheets("Draft Schedule Export").Cells(i, 2).Value, "HH:MM AM/PM")  'just hours, minutes and AM/PM
        Sheets("New Format").Cells(newFormatLine, 1).Value = currentdate                        'put the time in column A
        Sheets("New Format").Cells(newFormatLine, 3).Value = Sheets("Draft Schedule Export").Cells(i, 1).Value  'Put the Call name in column C
        Sheets("New Format").Cells(newFormatLine, 4).Value = Sheets("Draft Schedule Export").Cells(i, 4).Value  'Put the Notes in column D
        i = i + 1                                                                               'move to the next item in the same date
        newFormatLine = newFormatLine + 1                                                       'move down a line in the new format
    Loop
    newFormatLine = newFormatLine + 2                                                           'skip 2 lines
    i = i - 1                                                                                   'we went one too far
Next i

End Sub