How to copy and paste formulas in large worksheets Excel 2007.

[4joey1]

Hallo! I am using large volumes of data where I have to sum up related products in several worksheets.

Please see the excel sheet called Áutosum’to understand my query.

In worksheet called data_1, I have summed up all related products. I would like to copy and paste these sum formulas in worksheet data_2 such that in one single copy and paste action, all the related rows in data_2 will sum up like in data_1.

How can I achieve this?

Thanks.

[6StringJazzer]

Your workbook has 5 worksheets, but none of them are called data_1 or data_2.

[4joey1]

Oh!Sorry.I uploaded the wrong file.Please find the attached file Autosum 2.

The worksheets are called data1 and data2 respectively.

Thanks.

[6StringJazzer]

OK, now I see what you want to do. Let me elaborate for the sake of the rest of our audience.

This sheet has several columns of numbers, and the rows are grouped so that there are intermittent totals taken using the SUM function. Our user wants to copy just the SUM formulas and paste them into the corresponding places in the other sheet without touching the data.

Excel does not have a built-in way to copy a noncontiguous range and paste it so that it overlays existing cells. If you highlight all the SUM formulas individually (by holding CTRL and clicking), you can copy them all. But when you paste, they will all be pasted contiguously, instead of in the positions that correspond to their positions on Data1.

I have written a macro that can do this for you.

Public Sub CopySumFormulas()
   Dim c As Range
   
   For Each c In UsedRange
   
      If Mid(Worksheets("data1").Cells(c.Row, c.Column).Formula, 1, 4) = "=SUM" Then
         c.Formula = Worksheets("data1").Cells(c.Row, c.Column).Formula
      End If
   
   Next c
End Sub

If you are not familiar with macros, do the following:

1. In Excel, press ALT-F11. This will bring up the VBA development window; see the attached image.
2. On the left you will see a folder list. Look for your Excel file.
3. Underneath it you will see a list of the worksheets. Double-click on data2.
4. Then on the right side of the screen, paste the code above.
5. This is probably a one-time operation, so rather than put buttons on the worksheet, you can just position your cursor somewhere in the code, then press F5 to run the macro.