Help with populating a table with values on other sheets

[stormym]

This one has been driving me nuts. I am new to the VBA world so I am sure it is something stupid. But I am trying to basically create a summary table that references cells on different worksheets. The issue I seem to be having is when I try to set a formula for the cell, as I add new rows and additional data, all my rows are changing to the last populated data. If I use the value property, everything works without issue. Since I need referenced values instead of absolute values, I am using a formula to do reference the cell on another worksheet. Maybe I am doing it the hard way, but I just want to make this thing work.

Sub BuildSummaryTable()
    Dim sourceSh As Worksheet   'Source Sheet (Room page)
    Dim destSh As Worksheet     'Destination Sheet (Summary page)
    Dim destTbl As ListObject   'Summary Table
    Dim newRow As ListRow       'New Row
    Dim fHeader As String       'Formula Header
    


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    ' Set Constants
    Set destSh = ActiveWorkbook.Worksheets("Summary")
    Set destTbl = Range("SummaryTable").ListObject
    
    'Clear Current Table
    On Error Resume Next
    With destTbl
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.ClearContents
            .DataBodyRange.Delete
        End If
    End With
    
    ' Loop through all worksheets and copy the data to the
    ' summary worksheet.
    
    For Each sourceSh In ActiveWorkbook.Worksheets
        If sourceSh.Name <> destSh.Name And sourceSh.Visible = xlSheetVisible Then
            
            'Build Formula Header
            fHeader = "=" & "'" & sourceSh.Name & "'" & "!"
            
            'Add a new Row
            Set newRow = destTbl.ListRows.Add
            
            With newRow.Range
                .Cells(1, 1).Formula = fHeader & "$H$1"   'Set Status %
                .Cells(1, 2).Formula = fHeader & "$E$3"   'Set Room Name or Type
                .Cells(1, 3).Formula = fHeader & "$I$6"   'Set Project Scheduled Completion
                .Cells(1, 4).Formula = fHeader & "$I$2"   'Set Current Phase
                .Cells(1, 5).Formula = fHeader & "$G$10"  'Set UI Development Estimated Completion
                .Cells(1, 6).Formula = fHeader & "$G$16"  'Set Code Development Estimated Completion
                .Cells(1, 7).Formula = fHeader & "$G$23"  'Set Code Testing Estimated Completion
                .Cells(1, 8).Formula = fHeader & "$G$26"  'Set Onsite Testing Estimated Completion
                .Cells(1, 9).Formula = fHeader & "$I$3"   'Set Programming Estimated Completion
            End With
        End If
    Next


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

[p45cal]

You might have some luck with "If you want to disable formula replication altogether" about half way down this page:
http://www.k2e.com/tech-update/tips/...n-excel-tables

If that doesn't work, consider not using a Table.