Sub delCol()
Dim sourceSheet As Worksheet
Set sourceSheet = Sheet1
sourceSheet.Range("A:A, D:G, J:X ").EntireColumn.Delete
End Sub
Private Sub VLOOK_UP()
Dim I As Integer
For I = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
Worksheets("72 Hr").Cells(I, 5).Value = _
Application.WorksheetFunction.VLookup(Worksheets("72 Hr").Cells(I, 3).Value, _
Worksheets("3 LTR").Range("A:B"), 2, 1)
Next I
End Sub
Sub Delete_locals()
With ActiveSheet
.AutoFilterMode = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
.AutoFilter 1, "*CHS*"
On Error Resume Next
.Offset(0).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
.AutoFilter 1, "*777*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
Sub Add_Brackets()
With ActiveSheet
For Each cell In Range("C1", Range("C" & Rows.Count).End(xlUp))
cell.Value = "(" & cell.Value & ")"
On Error Resume Next
Next
End With
End Sub
Sub RemoveFirstThreeCharactersInEachCell()
For Each cell In Range("A1", Range("A65536").End(xlUp))
If Not IsEmpty(cell) Then
cell.Value = Mid(cell, 4, 4)
End If
Next cell
End Sub
Sub moveColumn()
With ActiveSheet
Columns("C").Cut
Columns("A").Insert shift:=xlToRight
Columns("E").Cut
Columns("B").Insert shift:=xlToRight
End With
End Sub
Sub concatMyData()
For I = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(I, "A").Value = Cells(I, "A").Value & " " & Cells(I, "B").Value
Next I
Columns(2).Delete
End Sub
Sub No_Departures()
Dim d1 As Date, d2 As Date
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
d1 = CDate(Split(Cells(r, "D"), " ")(1) & ", " & Split(Cells(r, "D"), " ")(0) & " " & Split(Cells(r, "D"), " ")(2))
d2 = CDate(Split(Cells(r - 1, "D"), " ")(1) & ", " & Split(Cells(r - 1, "D"), " ")(0) & " " & Split(Cells(r - 1, "D"), " ")(2))
If d1 - d2 >= 2 Then
Rows(r).Insert shift:=xlDown
Cells(r, "D") = Format(d1 - 1, "dd mmm yyyy 0700")
Cells(r, "A") = "NO DEPARTURES"
Cells(r, "B") = "N/A"
Cells(r, "C") = "N/A"
End If
Next
End Sub
Sub remove_hidden_Values()
Dim cell As Range
For Each cell In Selection
cell.Value = Trim(Replace(cell.Value, Chr(160), Chr(32)))
Next
End Sub
Sub Subtract7Hours()
Dim rng As Range
For Each rng In ActiveSheet.Range("D1", ActiveSheet.Cells(Rows.Count, 4).End(xlUp))
rng.NumberFormat = "dd mmm yyyy hhmm"
If IsNumeric(rng) Then
rng.Value = rng.Value - TimeSerial(7, 0, 0)
Else
rng.Value = CDate(Application.Replace(rng, Len(rng) - 1, 0, ":")) - TimeSerial(7, 0, 0)
End If
Next rng
End Sub
Sub InsertBlankRow()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Left(Range("D" & r).Value, 11) <> Left(Range("D" & r - 1).Value, 11) Then
Rows(r).Resize(1).Insert
End If
Next r
End Sub
Sub InsertRows()
Rows(1).Insert shift:=xlDown
End Sub
Sub Boarder()
Dim rngDB As Range
Dim rng2 As Range
Dim rng As Range, Target As Range
Dim a As Variant, b As Variant
Dim myDate As Date
Dim n As Long
Set rngDB = Range("b1", Range("d" & Rows.Count).End(xlUp))
Set rngDB = rngDB.SpecialCells(xlCellTypeBlanks)
rngDB.EntireRow.Insert
Set rng2 = rngDB.Offset(, 2)
a = Array("FLIGHT #", "A/C TYPE", "ROLL CALL")
b = Array("", "SEAT REL.", "REMARKS")
With rng2
.Value = b
.Font.Size = 12
.Font.Name = "Times New Roman"
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous '<~~cell's botton lines style
.Borders(xlEdgeBottom).Weight = xlMedium
End With
With rngDB
.Value = a
.Font.Size = 12
.Font.Name = "Times New Roman"
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous '<~~cell's botton lines style
.Borders(xlEdgeBottom).Weight = xlMedium
End With
myDate = Date
n = Date - DateSerial(Year(Date), 1, 0)
For Each rng In rngDB.Areas
Set Target = rng.Range("a1").Offset(, -1)
Target = UCase(Format(myDate, "dddd mmmm d") & " (" & n & ")")
myDate = myDate + 1
n = n + 1
Next rng
With rngDB.Offset(, -1)
.Font.Size = 12
.Font.Name = "Times New Roman"
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous '<~~cell's botton lines style
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End Sub
Sub Left_Align()
With Selection
.Font.Name = "Times New Roman"
.Font.Size = 10
.HorizontalAlignment = xlLeft
.BorderAround xlNone
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.EntireColumn.AutoFit
End With
Range("A1:G1").Select
End Sub
Sub Header()
Selection.Font.Bold = True
ActiveSheet.PageSetup.CenterHeader = _
"&""Times New Roman,Bold""&14Flight information is tentative and subject to change without notice" & Chr(10) & "&22DEPARTURES FROM CHS"
End Sub
Sub Footer()
Selection.Font.Bold = False
ActiveSheet.PageSetup.LeftFooter = Format(Time, "hh:mm")
End Sub
Sub columnWidth_D()
Worksheets("72 Hr").Range("F:F").columnWidth = 28
End Sub
Sub UPDATED_BY()
Sheets("72 hR").Range("A34") = "UPDATED BY:"
End Sub
Sub RunAll()
Call delCol
Call VLOOK_UP
Call Delete_locals
Call Add_Brackets
Call RemoveFirstThreeCharactersInEachCell
Call moveColumn
Call concatMyData
Call remove_hidden_Values
Call No_Departures
Call Subtract7Hours
Call InsertBlankRow
Call InsertRows
Call Boarder
Call Left_Align
Call Header
Call Footer
Call columnWidth_D
End Sub
