Refer to table name in a formula

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Refer to table name in a formula

Post by YasserKhalil »

Hello everyone
I have three tables in three sheets and I would like to put this formula after the table directly at the last column of it

Code: Select all

=SUBTOTAL(109,HR_2[Total])
My issue is how put the string "Total" in the columns AD to AG and in AH put the formula according to the table name
Untitled.png

I tried something like that but got an error

Code: Select all

.Range("AH" & p + 1).Formula = "=SUBTOTAL(109," & .ListObjects(1).Name & "[Total])"
Thanks advanced for help

The thread posted here too
https://www.excelforum.com/excel-progra ... ost5042490" onclick="window.open(this.href);return false;
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Refer to table name in a formula

Post by HansV »

Did the replies there help?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Refer to table name in a formula

Post by YasserKhalil »

Thanks a lot Mr. Hans
I didn't reach a solution yet ..

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Refer to table name in a formula

Post by HansV »

Could you post a sample workbook? I cannot download files from ExcelForum.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Refer to table name in a formula

Post by YasserKhalil »

Of course Mr. Hans
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Refer to table name in a formula

Post by HansV »

This works for me:

Code: Select all

Sub AddTotals()
    Dim wsh As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    For Each wsh In Worksheets
        For Each tbl In wsh.ListObjects
            Set rng = tbl.DataBodyRange
            With rng.Offset(rng.Rows.Count, rng.Columns.Count - 5).Resize(1, 4)
                .Merge
                .HorizontalAlignment = xlHAlignCenter
                .Interior.Color = RGB(0, 0, 128)
                .Font.Color = vbWhite
                .Font.Bold = True
                .Font.Size = 14
                .Value = "Total"
            End With
            With rng.Offset(rng.Rows.Count, rng.Columns.Count - 1).Resize(1, 1)
                .HorizontalAlignment = xlHAlignCenter
                .Interior.Color = vbYellow
                .Font.Color = RGB(128, 128, 128)
                .Font.Bold = True
                .Font.Size = 14
                .Formula = "=SUBTOTAL(109," & tbl.Name & "[Total])"
            End With
        Next tbl
    Next wsh
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Refer to table name in a formula

Post by YasserKhalil »

That's amazing Mr. Hans
Exactly as I was searching for. Thank you very very much