Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh table after code runs
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("Tem plate").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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh table after code runs
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 "Freddy" wrote in message ... 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("Tem plate").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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to refresh Child Pivot Table | Excel Discussion (Misc queries) | |||
Code To Refresh Query Table | Excel Discussion (Misc queries) | |||
pivot table refresh code failed | Excel Programming | |||
Code to Refresh Pivot Table | Excel Programming | |||
How To Finish pivot table refresh before cont. VBA code | Excel Programming |