Option Explicit
Sub macro_1()
Dim ws1 As Worksheet, ws2 As Worksheet, count1, count2
Set ws1 = Sheets("Original Output Format")
Set ws2 = Sheets.Add
count2 = 2
With ws2
.Range("B1") = "Flag 1 - Start"
.Range("D1") = "Flag 2 - R & D"
.Range("F1") = "Flag 3 - Dev"
.Range("H1") = "Flag 4 - Imp"
.Range("J1") = "Flag 5 - Go Live"
.Range("L1") = "Flag 6 - Close"
.Range("B1:C1").Merge
.Range("D1:E1").Merge
.Range("F1:G1").Merge
.Range("H1:I1").Merge
.Range("J1:K1").Merge
.Range("L1:M1").Merge
.Range("B1:M1").Font.ColorIndex = 3
.Range("B1:M1").Font.Bold = True
.Range("B1:M1").HorizontalAlignment = xlCenter
.Range("A2") = "Project"
.Range("B2:M2") = "Finish"
.Range("B2,D2,F2,H2,J2,L2") = "Start"
.Range("A2:M2").Font.Bold = True
End With
For count1 = 2 To ws1.Range("A" & Rows.Count).End(xlUp).Row
If ws1.Range("A" & count1) = ws1.Range("A" & count1 - 1) Then
ws2.Cells(count2, Columns.Count).End(xlToLeft).Offset(0, 1) = _
ws1.Range("C" & count1)
ws2.Cells(count2, Columns.Count).End(xlToLeft).Offset(0, 1) = _
ws1.Range("D" & count1)
Else
count2 = count2 + 1
ws2.Range("A" & count2) = ws1.Range("A" & count1)
ws2.Range("B" & count2) = ws1.Range("C" & count1)
ws2.Range("C" & count2) = ws1.Range("D" & count1)
End If
Next
ws2.Range("B3", Cells(Rows.Count, "M").End(xlUp)).NumberFormat = "m/d/yyyy"
End Sub
Bookmarks