[VBA] underscore in ListObject Name and Formula error

[Holiner]

Hello

I have a macro that create automatically new Sheets and on each Sheet create a Table
The Table Name is based on Sheet Name

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:G1"), , xlYes).Name = "Tab_" & ActiveSheet.Name

Excel replace space and other characters on the Sheet name with underscore _
Company X & Y became Tab_Company_X___Y

I want to make some synthesis from all the Table but my Formula doesn't work

TabName = Sheets("Company X & Y").ListObjects(1).Name
MyFormula= "=COUNTA(" & TabName & "[xxxxx])"       '[xxxxx] is the header name of a column
Cell(1, 2).Formula = MyFormula

This code work only if the Sheet name doesn't contain space or special characters

if I list all the ListObjects of my Workbook, those who have been renamed automatically appear without the underscore : Tab_Company X & Y

 

[DanteAmor]

You can store in a "control" sheet the name of the sheet and its name of Table.
As shown below:

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet Control</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:94.1px;" /><col style="width:131.17px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">SHEET</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">TABLE NAME</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Sheet1</td><td >Tab_Sheet1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Company X & Y</td><td >Tab_Company_X___Y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >company b c</td><td >Tab_company_b_c</td></tr></table>

With the following macro you can fill out the "Control" sheet.
On the red line put the special characters, they will be replaced by "_"

Sub test3()
    Dim shc As Worksheet, sh As Worksheet
    Dim i As Long, j As Long, hwName As String
    
    chars = [COLOR=#ff0000]Array("&", "-", " ")    'put special characters[/COLOR]
    i = 2
    Set shc = Sheets("Control")
    For Each sh In Sheets
        If sh.Name <> shc.Name Then
            shc.Cells(i, "A").Value = sh.Name
            wName = sh.Name
            For j = 0 To UBound(chars)
                wName = Replace(wName, chars(j), "_")
            Next
            shc.Cells(i, "B").Value = "Tab_" & wName
            i = i + 1
        End If
    Next
End Sub

---
Now create the table name with the following:

Sub CreateTable()
    Dim shc As Worksheet, wName As String, b As Range
    
    Set shc = Sheets("Control")
    Set b = shc.Range("A:A").Find([COLOR=#0000ff]ActiveSheet.Name[/COLOR])
    If Not b Is Nothing Then
        wName = b.Offset(0, 1)
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:G1"), , xlYes).Name = wName
    End If
End Sub

---
And in this way you can use your code:

Sub test2()
    TabName = Sheets("Company X & Y").ListObjects(1).Name
    MyFormula = "=COUNTA(" & TabName & "[G])"      '[xxxxx] is the header name of a column
    Cells(1, 10).Formula = MyFormula
End Sub

 

[Holiner]

Re: [Solved] [VBA] underscore in ListObject Name and Formula error

Thank you DanteAmor
it work perfectly

 

[DanteAmor]

Re: [Solved] [VBA] underscore in ListObject Name and Formula error

I'm glad to help you. Thanks for the feedback.