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
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
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
VBA Code:
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