'Align columns
Columns("A").ColumnWidth = 1.67
Columns("B").ColumnWidth = 3.11
Columns("C").ColumnWidth = 13.67
Columns("D").ColumnWidth = 6.11
Columns("E").ColumnWidth = 32.89
Columns("F").ColumnWidth = 6.56
Columns("G").ColumnWidth = 15.33
Columns("H").ColumnWidth = 11.78
Columns("I").ColumnWidth = 1.56


'Align Header Rows
Rows(1).RowHeight = 11.4
Rows(2).RowHeight = 10.8
Rows(3).RowHeight = 19.2
Rows(4).RowHeight = 21
Rows(5).RowHeight = 14.4
Rows(6).RowHeight = 14.4
Rows(7).RowHeight = 12
Rows(8).RowHeight = 12
Rows(9).RowHeight = 12
Rows(10).RowHeight = 12
Rows(11).RowHeight = 12
Rows(12).RowHeight = 27
Rows(13).RowHeight = 14.4
Rows(14).RowHeight = 14.4



'Get Account info for Header
'acctName = Sheets(1).Cells(21, 2).Value
'acctAddy = Sheets(1).Cells(22, 2).Value
'acctCity = Sheets(1).Cells(24, 2).Value
'acctProv = Sheets(1).Cells(26, 2).Value
'acctPostal = Sheets(1).Cells(27, 2).Value
'acctContact = Sheets(1).Cells(30, 4).Value
'acctPhone = Sheets(1).Cells(28, 4).Value
'acctFax = Sheets(1).Cells(29, 4).Value
'acctEmail = Sheets(1).Cells(31, 4).Value
'acctRep = Sheets(1).Cells(12, 2).Value
'Inject account info into Header
'Cells(7, 4).Value = acctName


Dim thisModel As String
Dim lastModel As String
Dim lastLocation As String
Dim thisTax As Double
Dim onTax As Double
Dim bcTax As Double
Dim mbTax As Double
Dim nfTax As Double
Dim ntTax As Double
Dim nsTax As Double
Dim nuTax As Double
Dim peTax As Double
Dim qcTax As Double
Dim skTax As Double
Dim ykTax As Double



'Sales tax Rates
abTax = 0.05
bcTax = 0.12
mbTax = 0.13
nbTax = 0.15
nfTax = 0.15
ntTax = 0.05
nsTax = 0.15
nuTax = 0.05
onTax = 0.13
peTax = 0.13
qcTax = 0.14975
skTax = 0.11
ykTax = 0.05



machineIndex = 16
modelQty = Sheets(2).Cells(machineIndex, 27).Value
moduleStart = 13
modelsToCheckStart = 33
standardSheetNumber = 16
mySheets = Worksheets.Count
statndardRowsInModelDesc = 15
modelTypes = Sheets(2).Cells(Rows.Count, "AB").End(xlUp).Row - statndardRowsInModelDesc



For i = standardSheetNumber To mySheets

    'Get
    thisLocation = Sheets(i).Cells(8, 2).Value
    thisProv = Sheets(i).Cells(10, 2).Value
    thisModel = Sheets(i).Cells(16, 2).Value


    'if address of this machine matches address of the last machine, check if the machine is the same model
    If thisLocation <> lastLocation Or thisModel <> lastModel Then

        'Set Quantity
        Cells(moduleStart, 2).Value = 1

        'Set Location
        Cells(moduleStart, 5).Value = thisLocation & " - " & thisProv

        'Set Province
        Cells(moduleStart, 6).Value = thisProv

        'Set Model
        Cells(moduleStart, 3).Value = thisModel

        'Set Tax
        Select Case thisProv
            Case "ON"
                thisTax = onTax
            Case "BC"
                thisTax = bcTax
            Case "MB"
                thisTax = mbTax
            Case "NB"
                thisTax = nbTax
            Case "NF"
                thisTax = nfTax
            Case "NT"
                thisTax = ntTax
            Case "NS"
                thisTax = nsTax
            Case "NU"
                thisTax = nuTax
            Case "PE"
                thisTax = peTax
            Case "AB"
                thisTax = abTax
            Case "QC"
                thisTax = qcTax
            Case "SK"
                thisTax = skTax
            Case "YK"
                thisTax = ykTax
        End Select

        'if quantity < 1 then add 1 to machineIndex
        If modelQty <= 1 Then
            machineIndex = machineIndex + 1
        Else
            modelQty = modelQty - 1
        End If

        'Increment line pointer
        moduleStart = moduleStart + 1

        lastLocation = thisLocation
        lastModel = thisModel

    Else
        'increment Qty and move to the next iteration
       Cells(moduleStart - 1, 2).Value = Cells(moduleStart - 1, 2).Value + 1
    End If


    For j = 0 To modelTypes - 1
        modelToCheck = Sheets(2).Cells(modelsToCheckStart + j, 18).Value

        If thisModel = modelToCheck Then
            ' set amount
            Cells(moduleStart - 1, 7).Value = Sheets(2).Cells(modelsToCheckStart + j, 20).Value / Sheets(2).Cells(modelsToCheckStart + j, 16).Value
            Cells(moduleStart - 1, 8).Value = Cells(moduleStart - 1, 7).Value * thisTax
            Exit For
        End If
    Next j

    'Multiply amount by quantity
    Cells(moduleStart - 1, 7).Value = Cells(moduleStart - 1, 7).Value * Cells(moduleStart - 1, 2).Value
    Cells(moduleStart - 1, 8).Value = Cells(moduleStart - 1, 8).Value * Cells(moduleStart - 1, 2).Value