Update table reference while code runs

[f2e4]

I have created a hidden worksheet containing a table I would like to use as a template.


Basically the table is 22 columns by 462 rows


462 names of places
22 letters


Example of code in cell B2:


=SUMIFS(S2974_1!$K$30:$K$39, S2974_1!$F$30:$F$39, $B5, S2974_1!$L$30:$L$39, C$3)


This code is then applied for the other 21 columns and 461 rows


An original table (S2974_1) exists on a different spreadsheet that has a table of costs that are manually entered by someone else.


I created a macro button on the S2974_1 page that will run the whole summary code when finished.


The theory is that there will be more than one original table in each spreadsheet depending on needs so I was wondering if I initially run the following code to copy the template table with the SUMIFS function from the hidden worksheet and paste in a new sheet, is it possible to update the "S2974_1" section of the function to the name of the page that the macro button was pressed.

Code to copy template and paste in new sheet


Dim wksNew As Worksheet
Set wksNew = Sheets.Add(After:=Sheets(Sheets.Count))Sheets("Template").Cells.Copy wksNew.Range("A1")


Example:

New original table called S2975_1 with macro button


Updated function on summary table:


=SUMIFS(S2975_1!$K$30:$K$39, S2975_1!$F$30:$F$39, $B5, S2975_1!$L$30:$L$39, C$3)