You can assign data from one range to another directly, or store it in an
array until needed

Sub test()
Dim rSource As Range, rDest1 As Range

Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3")
rSource.Value = "data"

Set rDest = ActiveWorkbook.Worksheets(2).Range("D4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

' copy data directly
rDest.Value = rSource.Value

' or store in an array until for later use, say after source wb has
closed
arr = rSource.Value

Set rDest = ActiveWorkbook.Worksheets(2).Range("H4")
With rSource
Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With

rDest.Value = arr
ActiveWorkbook.Worksheets(2).Activate
End Sub

Probably best not to copy more than say 20-50k cells this way in one go,
with larger sizes do in multiple steps

Regards,
Peter T
wrote in message
...
Hello All,

I've got a routine which opens 40 workbooks - copies in some data -
copies out some data - then closes each workbook saving changes. Each
Save takes approx 40sec due to the size of each wkbk and many
calculations involved. The macro therefore takes 25mins to run.

Not worrying too much about the length of time, I went ahead and wrote
the macro, as I thought I'd just run it in a second open instance of
Excel and still be able to do other things in another instance.

Problem is that the Copying & Pasting means that even if I'm working
on SQL Server, whilst this macro is running, if I try to copy and
paste in other applications it seems like the routine takes over the
clipboard. Sometimes if I copy/paste in another app the routine will
crash - again as I think the clipboard is causing a problem.

Anyone ever come across this before and know a way around it.

Any help much appreciated
Jason

(Using Excel 2007)