Dim mySheets As Long
Dim myActiveSheet As String
Dim standardSheetNumber As Long
Dim moduleStart As Long

Dim accountDetails As String
Dim accountNumber As Long
Dim Model As String
Dim CompanyName As String
Dim Address As String
Dim City As String
Dim State As String
Dim ZipCode As String

moduleStart = 0
standardSheetNumber = 19
mySheets = Worksheets.Count - 3

'Get account details
accountDetails = Sheets(1).Name
accountNumber = Sheets(accountDetails).Cells(17, 2).Value

'Create Layout
For i = standardSheetNumber To mySheets

Cells(moduleStart + 2, 1).Value = "Contract Type: CPC"
Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Merge
Range(Cells(moduleStart + 2, 1), Cells(moduleStart + 2, 9)).Font.Bold = True

Cells(moduleStart + 3, 1).Value = "Model"
Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Merge
Range(Cells(moduleStart + 3, 1), Cells(moduleStart + 3, 3)).Font.Bold = True

Cells(moduleStart + 5, 1).Value = "Additional Fixed Charge"
Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Merge
Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True

Cells(moduleStart + 6, 1).Value = "NO"
Cells(moduleStart + 7, 1).Value = "Fixed Charge Description (if applicable):"
Range(Cells(moduleStart + 7, 1), Cells(moduleStart + 7, 9)).Merge

Cells(moduleStart + 9, 1).Value = "Blk"
Range(Cells(moduleStart + 5, 1), Cells(moduleStart + 5, 3)).Font.Bold = True
Range(Cells(moduleStart + 8, 1), Cells(moduleStart + 10, 9)).Borders.LineStyle = xlContinuous

Cells(moduleStart + 10, 1).Value = "Clr"
Cells(moduleStart + 12, 1).Value = "SHIP TO:"
Range(Cells(moduleStart + 12, 1), Cells(moduleStart + 12, 9)).Merge
Cells(moduleStart + 12, 1).Font.Bold = True

Cells(moduleStart + 13, 1).Value = "Account #:"
Cells(moduleStart + 14, 1).Value = "Name"
Cells(moduleStart + 15, 1).Value = "Address"
Cells(moduleStart + 19, 1).Value = "Special Provisions:"
Range(Cells(moduleStart + 19, 1), Cells(moduleStart + 19, 8)).Merge

Cells(moduleStart + 6, 2).Value = "YES"
Cells(moduleStart + 8, 2).Value = "Image Charge"
Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 9)).Font.Bold = True
Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 4)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 8, 2), Cells(moduleStart + 8, 4)).Merge

Cells(moduleStart + 9, 2).Value = 0
Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 4)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 9, 2), Cells(moduleStart + 9, 4)).Merge

Cells(moduleStart + 10, 2).Value = 0
Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 4)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 10, 2), Cells(moduleStart + 10, 4)).Merge

Cells(moduleStart + 3, 4).Value = "Serial#"
Range(Cells(moduleStart + 4, 4), Cells(moduleStart + 4, 5)).Interior.ColorIndex = 6
Range(Cells(moduleStart + 3, 4), Cells(moduleStart + 3, 5)).Merge
Range(Cells(moduleStart + 3, 4), Cells(moduleStart + 3, 9)).Font.Bold = True

Cells(moduleStart + 5, 4).Value = "Fix Change Amount"
Range(Cells(moduleStart + 5, 4), Cells(moduleStart + 5, 9)).Font.Bold = True
Range(Cells(moduleStart + 6, 4), Cells(moduleStart + 6, 5)).Merge
Range(Cells(moduleStart + 6, 4), Cells(moduleStart + 6, 8)).Interior.ColorIndex = 6

Cells(moduleStart + 8, 5).Value = "Agreed Volume"
Range(Cells(moduleStart + 8, 5), Cells(moduleStart + 8, 5)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 8, 5), Cells(moduleStart + 8, 7)).Merge

Cells(moduleStart + 9, 5).Value = 0
Range(Cells(moduleStart + 9, 5), Cells(moduleStart + 9, 7)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 9, 5), Cells(moduleStart + 9, 7)).Merge

Cells(moduleStart + 10, 5).Value = 0
Range(Cells(moduleStart + 10, 5), Cells(moduleStart + 10, 5)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 10, 5), Cells(moduleStart + 10, 7)).Merge

Cells(moduleStart + 3, 6).Value = "Installed Date"
Range(Cells(moduleStart + 3, 6), Cells(moduleStart + 3, 8)).Merge
Range(Cells(moduleStart + 4, 6), Cells(moduleStart + 4, 8)).Merge

Cells(moduleStart + 5, 6).Value = "Billing Frequency"
Range(Cells(moduleStart + 5, 6), Cells(moduleStart + 5, 8)).Merge
Range(Cells(moduleStart + 6, 6), Cells(moduleStart + 6, 8)).Merge

Cells(moduleStart + 13, 6).Value = "Meter Read Contact:"
Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 9)).Merge
Range(Cells(moduleStart + 13, 7), Cells(moduleStart + 13, 9)).Borders(xlEdgeBottom).LineStyle = xlDash

Cells(moduleStart + 14, 6).Value = "Phone #:"
Range(Cells(moduleStart + 14, 7), Cells(moduleStart + 14, 9)).Merge
Range(Cells(moduleStart + 14, 7), Cells(moduleStart + 14, 9)).Borders(xlEdgeBottom).LineStyle = xlDash

Cells(moduleStart + 15, 6).Value = "Fax #:"
Range(Cells(moduleStart + 15, 7), Cells(moduleStart + 15, 9)).Merge
Range(Cells(moduleStart + 15, 7), Cells(moduleStart + 15, 9)).Borders(xlEdgeBottom).LineStyle = xlDash

Cells(moduleStart + 16, 6).Value = "Email:"
Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Merge
Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Borders(xlEdgeBottom).LineStyle = xlDash

'Cells beneath Email
Range(Cells(moduleStart + 16, 7), Cells(moduleStart + 16, 9)).Merge


Cells(moduleStart + 14, 7).Value = 0
Cells(moduleStart + 15, 7).Value = 0
Cells(moduleStart + 15, 7).Value = 0

Cells(moduleStart + 8, 8).Value = "Meter Start"
Range(Cells(moduleStart + 8, 8), Cells(moduleStart + 8, 8)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 8, 8), Cells(moduleStart + 8, 9)).Merge

'under meter start
Range(Cells(moduleStart + 9, 8), Cells(moduleStart + 9, 8)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 9, 8), Cells(moduleStart + 9, 9)).Merge

'second cell under meter start
Range(Cells(moduleStart + 10, 8), Cells(moduleStart + 10, 8)).HorizontalAlignment = xlCenter
Range(Cells(moduleStart + 10, 8), Cells(moduleStart + 10, 9)).Merge

Cells(moduleStart + 3, 9).Value = "Service Fee"

Cells(moduleStart + 4, 9).Interior.ColorIndex = 6

Cells(moduleStart + 5, 9).Value = "Initial Period (Mths)"

Cells(moduleStart + 6, 9).Value = 60
Cells(moduleStart + 6, 9).HorizontalAlignment = xlLeft

Cells(moduleStart + 19, 9).Value = "Customer Intial:"

    'Get Sheet name
    myActiveSheet = Sheets(i).Name

    'Get Model
    Range(Cells(moduleStart + 4, 1), Cells(moduleStart + 4, 3)).Merge
    Model = Sheets(myActiveSheet).Cells(16, 2).Value
    Cells(moduleStart + 4, 1).Value = Model

    'insert Account number
    Range(Cells(moduleStart + 13, 2), Cells(moduleStart + 13, 5)).Merge
    Range(Cells(moduleStart + 13, 2), Cells(moduleStart + 13, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
    Cells(moduleStart + 13, 2).Value = accountNumber
    Cells(moduleStart + 13, 2).HorizontalAlignment = xlLeft

    'Get CompanyName from sheet
    Range(Cells(moduleStart + 14, 2), Cells(moduleStart + 14, 5)).Merge
    Range(Cells(moduleStart + 14, 2), Cells(moduleStart + 14, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
    CompanyName = Sheets(myActiveSheet).Cells(7, 2).Value
    Cells(moduleStart + 14, 2).Value = CompanyName

    'Get Address from sheet
    Range(Cells(moduleStart + 15, 2), Cells(moduleStart + 15, 5)).Merge
    Range(Cells(moduleStart + 15, 2), Cells(moduleStart + 15, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
    Address = Sheets(myActiveSheet).Cells(8, 2).Value
    Cells(moduleStart + 15, 2).Value = Address

    'Get City from sheet
    Range(Cells(moduleStart + 16, 2), Cells(moduleStart + 16, 5)).Merge
    Range(Cells(moduleStart + 16, 2), Cells(moduleStart + 16, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
    City = Sheets(myActiveSheet).Cells(9, 2).Value
    Cells(moduleStart + 16, 2).Value = City

    'Get State from sheet
    State = Sheets(myActiveSheet).Cells(10, 2).Value

    'Get ZipCode from sheet
    Range(Cells(moduleStart + 17, 2), Cells(moduleStart + 17, 5)).Merge
    Range(Cells(moduleStart + 17, 2), Cells(moduleStart + 17, 5)).Borders(xlEdgeBottom).LineStyle = xlDash
    ZipCode = Sheets(myActiveSheet).Cells(11, 2).Value
    Cells(moduleStart + 17, 2).Value = State & " - " & ZipCode

    moduleStart = moduleStart + 20

Next i