VBA to create a tabular table from a data table

[George_Martin_3]

Hi,
I already have a chunk of code that creates the tabular table, but what I need is to insert a new data point that is not in data set.
-
For the example data below, I need a column after STATE, named METHOD, that says GROUND for columns 5 and 6, and in that same column for column 7 to say AIR.
-
Thank you for any assist.

    Sub ExampleCode()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim recRow As Long
    Dim lastRow As Long
    Dim i As Long
    Dim lngCol As Long


    'Where does the data come from?
    Set wsSource = Worksheets("Sheet1")


    Application.ScreenUpdating = False


    'Create new sheet
    Set wsDest = ThisWorkbook.Worksheets.Add


    'Setup headers
    wsSource.Range("A1:D1").Copy wsDest.Range("A1")
    wsDest.Range("E1").Value = "Pivot Field Dimension"
    wsDest.Range("F1").Value = "PF Value"
    recRow = 2


    With wsSource
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


        'Loop over our data
        For i = 2 To lastRow
            'There are 3 items per main line. In columns 5-7
            For lngCol = 5 To 7
                'Grab the four columns every time
                wsSource.Cells(i, 1).Resize(1, 4).Copy wsDest.Cells(recRow, 1)
                wsDest.Cells(recRow, 5).Value = wsSource.Cells(1, lngCol).Value
                wsDest.Cells(recRow, 6).Value = wsSource.Cells(i, lngCol).Value


                'Incremenet counter for output
                recRow = recRow + 1
            Next lngCol
        Next i
    End With


    Application.ScreenUpdating = True
End Sub

START<code id="output" data-bind="text: output()" style="padding: 0px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; font-size: 12px; color: inherit; border-radius: 3px; background-color: transparent; border: 0px;">
</code><code id="output" data-bind="text: output()" style="padding: 0px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; font-size: 12px; color: inherit; border-radius: 3px; background-color: transparent; border: 0px;">

Type	Group	Part	State	Train	Truck	Airplane
Office	Furtniture	ABC1	TX	X		X
Patio	Utility	DEF2	FL		X	X
Bedroom	Electronics	GHI3	CO	X	X

<tbody>
</tbody>

FINISH
</code><code id="output" data-bind="text: output()" style="padding: 0px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; font-size: 12px; color: inherit; border-radius: 3px; background-color: transparent; border: 0px;">

Type	Group	Part	State	Method	Column	Value
Office	Furniture	ABC1	TX	GROUND	Train	X
*******	Utility	DEF2	FL	GROUND	Train
Bedroom	Electronics	GHI3	CO	GROUND	Train	X
Office	Furniture	ABC1	TX	GROUND	Truck
*******	Utility	DEF2	FL	GROUND	Truck	X
Bedroom	Electronics	GHI3	CO	GROUND	Truck	X
Office	Furniture	ABC1	TX	AIR	Airplane	X
*******	Utility	DEF2	FL	AIR	Airplane	X
Bedroom	Electronics	GHI3	CO	AIR	Airplane

<tbody>
</tbody>

</code>