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
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
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

I have a macro that create automatically new Sheets and on each Sheet create a Table
The Table Name is based on Sheet Name
Code:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:G1"), , xlYes).Name = "Tab_" & ActiveSheet.Name
Company X & Y became Tab_Company_X___Y
I want to make some synthesis from all the Table but my Formula doesn't work
Code:
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

