Sub FormatBOM()

'
' AddPage'
'
''' This function copies the BOM and pastes all data to a new sheet to be formated as neccessary.

    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste

'
'Deleting Sheet Headers'
'
'''6 length


'
'GeneralFormatingOfBOM '
'
'''Re-Format Top of BOM and Delete Top Header'

    Range("A3").Select
    Selection.Cut Destination:=Range("B17")
    Range("J6").Select
    Selection.Cut Destination:=Range("U17")
    Range("D6").Select
    Selection.Cut Destination:=Range("C17")
    Rows("2:16").Select
    Range("A16").Activate
    Selection.Delete Shift:=xlUp
    Range("M1").Select
    Selection.Delete Shift:=xlToLeft


'''Delete Uneccessary Lines (Empty lines then headers)
    selected_row = 3
    Do While (Range("A" & selected_row) <> vbNullString Or Range("A" & selected_row + 1) <> vbNullString Or Range("A" & selected_row + 2) <> vbNullString Or Range("A" & selected_row + 3) <> vbNullString Or Range("A" & selected_row + 4) <> vbNullString Or Range("A" & selected_row + 5) <> vbNullString)
        If (Range("A" & selected_row) = vbNullString) Then
            Rows(selected_row & ":" & selected_row).Select
            Selection.Delete Shift:=xlUp
        End If
        selected_row = selected_row + 1
    Loop

    selected_row = 3
    BOM_Length = 2
    Do While (Range("A" & selected_row) <> vbNullString Or Range("A" & selected_row + 1) <> vbNullString Or Range("A" & selected_row + 2) <> vbNullString Or Range("A" & selected_row + 3) <> vbNullString Or Range("A" & selected_row + 4) <> vbNullString Or Range("A" & selected_row + 5) <> vbNullString)
        If (Range("A" & selected_row) = "ZCOSTBOM") Then
            Rows(selected_row - 2 & ":" & selected_row + 5).Select
            Selection.Delete Shift:=xlUp
        End If
        selected_row = selected_row + 1
    Loop


''' Delete footer
    selected_row = selected_row - 5
    Rows(selected_row + 1 & ":" & selected_row + 4).Select
    Selection.Delete Shift:=xlUp


'''Adding beginning of BOM and rearragement of columns
    Columns("A:AB").Select
    Selection.Cut Destination:=Columns("F:AG")
    Range("G2").Select
    Selection.Cut Destination:=Range("A2")
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("V:V").Select
    Selection.Cut Destination:=Columns("K:K")
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").EntireColumn.AutoFit
    Columns("M:Z").Select
    Selection.Delete Shift:=xlToLeft
    Range("M3").Select
    Selection.Delete Shift:=xlUp
    Range("O13").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp


    selected_row = 3
    Do While (Range("F" & selected_row) <> vbNullString)
        If (Range("F" & selected_row) <> 0.1 And Not Range("F" & selected_row) Like "..*") Then
            Rows(selected_row & ":" & selected_row).Select
            Selection.Delete Shift:=xlUp
        Else
        selected_row = selected_row + 1
        End If

    Loop
    BOM_Length = selected_row - 1
'
'FormatTopLevelRow Macro'
'   Add PN to Top Level PN Column, zero BOM Level added, and delete top row'

    Range("I1").Select
    Selection.Copy
    Range("E1").Select
    ActiveSheet.Paste
    Range("F1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0"



'
'Level Macro'
'''Adds New Columns to apply formula, paste values, delete columns'
'

    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],1)"
    Range("G1").Select
    Selection.AutoFill Destination:=Range("G1:G" & BOM_Length), Type:=xlFillDefault
    Range("G1:G" & BOM_Length).Select
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft


'
'MakePurchase Macro'
'
'
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "=IF(R[1]C[-8]<>0,RC[-8]-R[1]C[-8],0)"
    Range("N1").Select
    Selection.AutoFill Destination:=Range("N1:N" & BOM_Length), Type:=xlFillDefault
    Range("N1:N" & BOM_Length).Select
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<0,""M"",""P"")"
    Range("O1").Select
    Selection.AutoFill Destination:=Range("O1:O" & BOM_Length), Type:=xlFillDefault
    Range("O1:O" & BOM_Length).Select
    Columns("O:O").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

'
' FillInFirst4Columns Macro'
'
'

    Range("B1").Select
    FTA_Type = InputBox(Prompt:="FTA Type.", _
    Title:="ENTER FTA Type", Default:="NAFTA")
    Blanket_Period = InputBox(Prompt:="Blanket Period.", _
    Title:="ENTER Blanket Period", Default:="2014")
    Entity_Name = InputBox(Prompt:="Entity.", _
    Title:="ENTER Entity Name", Default:="OSMONICS")
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = Blanket_Period
    Range("C1").Select
    ActiveCell.FormulaR1C1 = FTA_Type
    Range("D1").Select
    ActiveCell.FormulaR1C1 = Entity_Name
    Range("A1:E1").Select
    Selection.Copy
    Range("A2:E2").Select
    ActiveSheet.Paste
    Range("A1:E2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A1:E" & BOM_Length), Type:=xlFillDefault