VBA Naming a table with a variable(sheet name)

[dannyh]

Hi All

I need to run a piece of code once or twice a month on a sheet (with that months data) then the following month run the same code on a new sheet(with that months data)

Within my code I create a table that is then referenced in a summary section, I need each table on each sheet to have a different name as I have found otherwise the summary section picks up values from previous months sheet.

I have tried a few different options with my last being to define two parts of the table name (one fixed and one variable) as strings and then put them together, I am now getting the following error

Function call on left hand side of the assignment must return Variant or Object.

I think this is because I have tried to define my variable as a string but I'm not sure and after messing about with the code for a day I need help

Dim shtName As String
    shtName = ActiveSheet.Name
Dim tblName As String
    tblName = "_AgingTable"
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
        shtName & " AgingTable"
    
    Range("I3").Select
    ActiveCell.Formula = "=SUM(J3:O3)"
    Range("J3").Select
    ActiveCell.Formula = "=SUMIFS(Nov_19_AgingTable[Amount Due],Nov_19_AgingTable[Overdue Days],""<1"")"
    Range("K3").Select
    ActiveCell.Formula = "=SUMIFS(Nov_19_AgingTable[Amount Due],Nov_19_AgingTable[Overdue Days],"">=1"",Nov_19_AgingTable[Overdue Days],""<=30"")"
    Range("L3").Select
    ActiveCell.Formula = "=SUMIFS(shtName & tblName[Amount Due],shtName & tblName[Overdue Days],"">=31"",shtName & tblName[Overdue Days],""<=60"")"

This the piece of code in question with the error occurring on the bottom line, I removed the two strings and hard typed the correct table name in the lines above to prove that it worked correctly.

Can anyone tell me what my error is ?

Thanks Dan

 

[Fluff]

Try it like

Dim shtName As String
    shtName = ActiveSheet.Name
Dim tblName As String
    tblName = shtName & "_AgingTable"
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
        tblName
    
'    Range("I3").Formula = "=SUM(J3:O3)"
'    Range("J3").Formula = "=SUMIFS(Nov_19_AgingTable[Amount Due],Nov_19_AgingTable[Overdue Days],""<1"")"
'    Range("K3").Select
'    ActiveCell.Formula = "=SUMIFS(Nov_19_AgingTable[Amount Due],Nov_19_AgingTable[Overdue Days],"">=1"",Nov_19_AgingTable[Overdue Days],""<=30"")"
    Range("L3").Formula = "=SUMIFS(" & tblName & "[Amount Due]," & tblName & "[Overdue Days],"">=31""," & tblName & "[Overdue Days],""<=60"")"

 

[dannyh]

Hi Fluff

Thanks for your quick response I have changed my code accordingly but am still getting a run-time error 1004 - Application-defined or Object-defined error on the same line.

Dan

 

[Fluff]

Did you change this line

tblName = shtName & "_AgingTable"

& this one

ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
        tblName

as well, or just the line with the formula?

 

[dannyh]

yes my new code is this

    Dim shtName As String
    shtName = ActiveSheet.Name
    Dim tblName As String
    tblName = shtName & "_AgingTable"
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = _
        tblName
           
    Range("I3").Formula = "=SUM(J3:O3)"
    Range("J3").Formula = "=SUMIFS(Nov_19_AgingTable[Amount Due],Nov_19_AgingTable[Overdue Days],""<1"")"
    Range("K3").Formula = "=SUMIFS(Nov_19_AgingTable[Amount Due],Nov_19_AgingTable[Overdue Days],"">=1"",Nov_19_AgingTable[Overdue Days],""<=30"")"
    Range("L3").Formula = "=SUMIFS(" & tblName & "[Amount Due]," & tblName & "[Overdue Days],"">=31""," & tblName & "[Overdue Days],""<=60"")"

 

[Fluff]

In that case check that your two column headers are correct.

 

[dannyh]

They are, I double checked but I assume the two lines above wouldn't work if they were wrong ?

I have included a small snapshot of my data, the overdue days have been adjusted in order to produce a value if the formula works correctly, does this work for you ?

Thanks Dan

Date	Customer	Amount Due	Due on	Overdue Days
29-Nov-2019	A	34220.00	29-Nov-2019	1​
29-Nov-2019	B	2790.00	29-Nov-2019	1​
29-Nov-2019	C	217991.01	29-Nov-2019	5​
29-Nov-2019	D	12992.00	29-Nov-2019	11​
29-Nov-2019	E	27140.00	29-Nov-2019	12​
28-Nov-2019	F	2237.00	28-Nov-2019	18​
27-Nov-2019	G	285273.90	27-Nov-2019	29​
27-Nov-2019	H	29500.00	27-Nov-2019	29​
26-Nov-2019	I	25960.00	26-Nov-2019	33​
26-Nov-2019	J	29500.00	26-Nov-2019	35​
25-Nov-2019	K	11800.00	25-Nov-2019	40​
25-Nov-2019	L	20060.00	25-Nov-2019	55​
25-Nov-2019	M	64900.00	25-Nov-2019	55​
25-Nov-2019	N	70800.00	25-Nov-2019	58​
25-Nov-2019	O	43660.00	25-Nov-2019	59​
25-Nov-2019	P	53100.00	25-Nov-2019	60​
21-Nov-2019	Q	4140.00	21-Nov-2019	62​
20-Nov-2019	R	23600.00	20-Nov-2019	68​
20-Nov-2019	S	94400.00	20-Nov-2019	70​
20-Nov-2019	T	2230.00	20-Nov-2019	70​
20-Nov-2019	U	82600.00	20-Nov-2019	73​
19-Nov-2019	V	162424.64	19-Nov-2019	76​

 

[dannyh]

I'm really confused now, when I check the line of code that errors it looks correct, here is a side by side of the line above and the line errors.

 

[Fluff]

That suggests that your sheet is called Nov'19 rather than Nov_19, which would explain things.

 

[dannyh]

Hi Fluff

It does indeed, that's what happens when you have been starring at the screen too long !

It doesn't like the apostrophe but if I rename my sheet Nov_19 it works fine, any ideas why as a matter of interest ?

Thanks for the help !