Is there a way to get an automate scrip to read a value in a given cell on one worksheet, and then find that value in row 1 of a second worksheet and then copy & paste values for all cells in that column?

• The date value of the data is always in cell A1 of Worksheet 1
• Formulas transpose the data from Worksheet 1 into worksheets 2 to 38 according to the date in cell A1 of Worksheet 1
• So that new data can be inserted into Worksheet 1, I need to overwrite the formulas in the correct columns on worksheets 2 to 38 with the values (copy & paste values)

If there is a solution using an automate script that would be great. Using a product other than excel isn’t an option, the data has to be retained in excel on our SharePoint site for governance reasons.