Exam Prep - VBA

Sub Main()
    Dim lastRow, lastColumn As Integer
    lastRow = Cells(Rows.Count, 1).End(xlUp).row
    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    Sheets.Add(Before:=Sheets("Sheet1")).name = "Copy"

    CopyData (lastColumn)
    CalculateExpenses (lastRow)
    CalculateProfit (lastRow)

    Dim colIndex As Variant
    colIndex = GetColumnAddress("Copy", "Contract Price")
    InsertColumn colIndex, "Expected Delivery Date"
    CalculateExpectedDeliveryDate (lastRow)

    InsertColumn 1, "Full Name"
    InsertFullName (lastRow)

    Dim colIndexDel As Integer
    colIndexDel = GetColumnAddress("Copy", "First Name")
    Columns(colIndexDel).EntireColumn.Delete
    Columns(colIndexDel).EntireColumn.Delete

    Formatting

    Range("E" & lastRow + 2) = "=sum(E3:E" & lastRow + 1 & ")"
    Range("F" & lastRow + 2) = "=sum(F3:F" & lastRow + 1 & ")"
    Range("G" & lastRow + 2) = "=sum(G3:G" & lastRow + 1 & ")"

    Range("A1").Select
End Sub



Sub InsertFullName(lastRowParam As Integer)
    For i = 2 To lastRowParam
        Dim firstName, lastName As String
        firstName = Range("B" & i)
        lastName = Range("C" & i)
        Range("A" & i) = firstName & " " & lastName
    Next
End Sub

Sub CalculateExpectedDeliveryDate(lastRowParam As Integer)
    '=D2+7
    For i = 2 To lastRowParam
        Range("E" & i) = "=D" & i & "+7"
    Next

End Sub

Function GetColumnAddress(sheetName, targetColName As String)
    Set col = Sheets(sheetName).Rows(1).Find(What:=targetColName)
    If col Is Nothing Then
        GetColumnAddress = False
    Else
        GetColumnAddress = col.Column
    End If
End Function

Sub InsertColumn(colIndex As Variant, nameColPar As String)
    Sheets("Copy").Select

    Columns(colIndex).EntireColumn.Insert
    Cells(1, colIndex).Value = nameColPar
End Sub

Sub CalculateProfit(lastRowParam As Integer)
    For i = 2 To lastRowParam
        '=E2-F2
        Range("G" & i) = "=E" & i & "-F" & i
    Next

End Sub

Sub CalculateExpenses(lastRowParam As Integer)
    '=E2*0.02
    Sheets("Copy").Select
    For i = 2 To lastRowParam
        Dim contractPrice As Double
        contractPrice = Range("E" & i)

        If contractPrice < 10000 Then
            Range("F" & i) = "=E" & i & "*3%"
        ElseIf contractPrice <= 30000 Then
            Range("F" & i) = "=E" & i & "*2.8%"
        ElseIf contractPrice <= 100000 Then
            Range("F" & i) = "=E" & i & "*2.5%"
        Else
            Range("F" & i) = "=E" & i & "*2%"
        End If

    Next
End Sub

Sub CopyData(lastColumnParam As Integer)
    Sheets("Copy").Select
    For i = 1 To lastColumnParam
        Sheets("Sheet1").Select
        Columns(i).Select
        Selection.Copy
        Sheets("Copy").Select
        Columns(i).Select
        ActiveSheet.Paste
    Next
End Sub

Sub Formatting()
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1:G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Clients And Contracs"
    Range("A1:G101").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    ActiveWindow.SmallScroll Down:=87
    Range("A101:D101").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    ActiveCell.FormulaR1C1 = "TOTAL:"
    Range("E101").Select
    Range("A1:G1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = -0.249977111117893
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
    End With
    Selection.Font.Bold = True
    Selection.Font.Size = 16
    Range("A2:G2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .ReadingOrder = xlContext
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .ReadingOrder = xlContext
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .ReadingOrder = xlContext
    End With
    ActiveWindow.SmallScroll Down:=88
    Range("A101:G101").Select
    Selection.Font.Bold = True
    Range("A1").Select
End Sub