Refresh table after code runs

[Freddy]

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

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

 

[Mike Fogleman]

You could do a Find/Replace on the formulas. The ReplaceWith text would be
easy enough: NewSheet.Name. The FindWhat text may not be so easy. If the new
sheet is always named one number higher in the 4-digit number, then perhaps
you could strip down the new sheet name to the 4 digits, subtract 1, then
rebuild the old sheet name to use for the FindWhat text. Unless you see an
easier way to reference the old sheet name, then this would be a path to
try.

Mike F