Dim mySheets As Long
Dim myActiveSheet As String
Dim standardSheetNumber 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


standardSheetNumber = 18
mySheets = Worksheets.Count - 1

For i = standardSheetNumber To mySheets
    'Get Sheet name
    myActiveSheet = Sheets(i).Name

    'Get Model
    Model = Sheets(myActiveSheet).Cells(16, 2).Value
    Cells(i, 1).Value = Model

    'Get CompanyName from sheet
    CompanyName = Sheets(myActiveSheet).Cells(7, 2).Value
    Cells(i, 2).Value = CompanyName

    'Get Address from sheet
    Address = Sheets(myActiveSheet).Cells(8, 2).Value
    Cells(i, 3).Value = Address

    'Get City from sheet
    City = Sheets(myActiveSheet).Cells(9, 2).Value
    Cells(i, 4).Value = City

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

    'Get ZipCode from sheet
    ZipCode = Sheets(myActiveSheet).Cells(11, 2).Value
    Cells(i, 6).Value = ZipCode
Next i