Is it possible to copy multiple but separate formulae worksheet lines at the same time, between different worksheets?

  • I have made a mistake creating an Excel workbook containing 12 worksheets. I am using a legal copy of Excel 2007.


    I have a new Excel workbook to record and analyse weather statistics; temperature, humidity and similar data.


    There are 12 worksheets, each one for a single year.


    Each worksheet has 12 months, each month consisting of 16 rows and 31 columns per month, with blank cells at the end of rows for months with 28 and 30 days. The error is in one single line in each month.


    While doing some back record conversion I have realised that in my master blank worksheet, and consequentially every copy there is an error for February.


    A key Cardinal temperature for farmers and growers is the 11 day rolling average temperature. It is easy to create cell formulae to add 11 cell's data then divide by 11 to correct the error. The problem is then how to copy the amended row with 30 or 31 formulae to all the other sheets.


    I have realised that when I created the master sheet, I introduced an error in one line of formulae because I used 30 days for February not 28/29. This means that for March to December, in one row for each of the 10 months there is an error.


    Is there a way to copy 10 separate rows of amended formulae (1 in March, April etc) in one copy and paste operation to each worksheet? Or do I have to copy the 10 amended rows individually into each of the 12 year's worksheet? ie 120 separate copy and paste operations. So is it 12 copy and pastes or `120?


    Thank you for reading

  • Can you attach a workbook with one of your 12 worksheets and indicate what formula needs to be changed (and what you want it change to).


    It may be possible to write a macro, that would need to be run just once, to update the formulae in all the worksheets.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Can you attach a workbook with one of your 12 worksheets and indicate what formula needs to be changed (and what you want it change to).


    It may be possible to write a macro, that would need to be run just once, to update the formulae in all the worksheets.

    Thank you for replying, I have attached a slimmed down sheet.

    Weather statistics 2022 ERROR.xlsx

    The "Blank Year" workbook has the 12 now corrected rows highlighted in yellow. The 2022 workbook is uncorrected so all 12 rows have errors at the start of the month based on how many days there are at the end of the previous month.

    In addition to 12 individual "Year" workbooks there are three additional books in the worksheet which aggregate all the individual year's data in one place and do more analysis and chart the results. These DO NOT need any amendments.

  • The "Blank Year" sheet is for the Year 2000, so I assume February 29th should be there and formulae as required in rows 27 to 39 for Column AD.


    What years do you need workbooks for?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • There is an active workbook for each year. Every December I copy the blank year, rename to the new year then the blank remain untouched. I have a second blank called leapyear blank. New century years are not generally leap years. I called it 2000 just to easily show it is the master worksheet.

    Data is only entered once in a worksheet to be efficient, then is used across a range of statistical graphs and charts to show climate breakdown effects. Every worksheet is an exact replica of blank year.


    In blank worksheet, the 12 rows highlighted in yellow, 8, 28, 48, 68 etc to 228 have had thee formulae corrected. Because cells B28, C28 to K28 add a value from the corresponding row for the previous month above, I need to copy cells B*8 to AF*8, so all 12 rows of 31 cells in the blank, correct worksheet, to corresponding rows in all the annual sheets, like 2022 where there is an error on all 12 rows for "11 Day Avge".


    I can copy one row at a time, but it is hardly efficient. So I am asking if there is a way to select the 31 cells, in the 12 separate rows of the blank, so 372 cells in total, in blocks of 31 and then copy them all in one go to each active worksheet?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!