PDA

View Full Version : Help with populating a table with values on other sheets



stormym
04-26-2017, 12:35 PM
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
04-26-2017, 02:28 PM
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/823-tips-disabling-formula-replication-excel-tables

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