Hi, I am using a macro to add a column in two tables, and for my table "tbl2" im wanting the formula for the newly added column (datarow cell, it is only one row plus a header row) to equal the total of the column i added in tbl which is in a different sheet. tbl name is "Time_Log_Table3" so it would look like "=Time_Log_Table3[[#Totals],[whaterver tbl column name is]]. Here is my code that is working up to the point where it says "need help here" how do i get this to write out correctly?
VBA Code:
Sub EWC()
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim tbl As ListObject
Dim tbl2 As ListObject
Dim rngColumn As Range
Dim ncolumn As Integer
Dim Op As String
Dim SNum As String
Set sh = Sheets("Time Log Test")
Set sh2 = Sheets("Work Order")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.AutoCorrect.AutoFillFormulasInLists = False
LaborOpName = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
Set tbl = sh.ListObjects("Time_Log_Table3")
With tbl
ncolumn = WorksheetFunction.Match("Hours", tbl.HeaderRowRange, 0)
'Asign the selected op text to variable Op
Op = LaborOpName
sh.Unprotect
tbl.ListColumns.Add (ncolumn)
tbl.HeaderRowRange(, ncolumn).Value = Op
tbl.ListColumns(ncolumn).TotalsCalculation = xlTotalsCalculationSum
tbl.ListColumns(ncolumn).Range.NumberFormat = "0.00"
Set quoted_time_cell = tbl.TotalsRowRange(, ncolumn).Offset(-1, 0)
Set subtotal_cell = tbl.TotalsRowRange(, ncolumn)
subtotal_cell.Value = subtotal_cell.Formula & "-" & quoted_time_cell.Address
End With
With tbl2
ncolumn = WorksheetFunction.Match("Total Hours", tbl2.HeaderRowRange, 0)
sh.Unprotect
tbl2.ListColumns.Add (ncolumn)
tbl2.HeaderRowRange(, ncolumn).Value = tbl.HeaderRowRange(, ncolumn).Value
[B] 'need help here, the "tbl.TotalsRowRange(, ncolumn).Address" isnt giving me the correct result i want[/B]
tbl2.DataBodyRange(, ncolumn).Formula = "=" & tbl.TotalsRowRange(, ncolumn).Address
tbl2.ListColumns(ncolumn).Range.NumberFormat = "0.00"
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.AutoCorrect.AutoFillFormulasInLists = True
End Sub