Copying Large Amounts of Data in Excel

Question

I've got a spreadsheet with around 90 identically formatted workbooks. I need to copy and paste around 336 independant formulas located in one row from a completed worksheet to all the other worksheets. Unfortunatley, this ends up being around 30k indvidual cells that need to be copied and pasted at once. Is there a workaround other than splitting it up and doing it manually?

Any help is apperciated. Thanks.

Copy the formulas, select all the other sheets (use Shift+Click on the tabs) then you can paste to all the other sheets at once. — Tim Williams
I've done that, the problem is is that my system ooes not have enough RAM/ Excel doesn't use enough of my systems resources to properlly do it. Each time I try pasting into the grouped worksheets, Excel crashes and closes. Upon re-opening Excel, I try to access the recovered document, but it always says that it is corrupt. — user2829172
What if you record a macro of copying and pasting to one worksheet and then modify the code to loop through one sheet at a time when pasting. — Automate This
Try @PortlandRunner's suggestion, then post your code here in your original question (edit it to include the code). This can most likely be done without "copy/paste" -- a common mistake is to rely to heavily on familiar actions like "copy" and "paste" when there are other methods that essentially "copy" while using fewer resources. — David Zemens
Just turn off calculation (set it to Manual) before you do the copy-paste. The issue may be that it is trying to calculate a whole bunch of really complex formulas at once and is hanging because it will take an hour to compute. Doing them with calculation off should let you paste, and should save a lot of resources because it isn't recalculating all the previous sheets each time it performs a paste action (it will just recalculate all sheets once). — jmac

Jose M. Jose M. · Accepted Answer · 2013-09-30T02:54:02

You can try using VBA. Something like the procedure below should get you started. The procedure below takes the information from. This procedure assumes that your formulas begin in Row A1 and end in C1, change as you need. Inside the array, list the name of the sheets you want.

Sub copyFormulas()

Dim rng As Range
Dim WS As Worksheet

With Sheets("The Sheet Name with the Formulas To Copy")

      Set rng = .Range(.Range("A1:C1"), Range("A" & Rows.Count).End(xlUp))

End With

    For Each WS In Sheets(Array("Your Destination Sheet Name Here", "And Here")) 'Add more sheets if you need to
                    WS.Rows(2).Resize(rng.Count).Copy
                    rng.Copy Destination:=WS.Range("A1")

            Next WS

End Sub

Copying Large Amounts of Data in Excel

1 Answers