How to issue a table column cell formula to "equal" a different table column total cell on a different worksheet with vba

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,127,585
Messages
5,625,656
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top