Hi,

Could you clarify "pre-format the destination sheet?"

Each cell will have it's own formatting.... so...

workbook worksheet address value
1.xls Sheet1 $A$1 Hello
1.xls Sheet1 $A$2 World
2.xls Sheet1 $J$10 10
2.xls Sheet2 $Z$100 32,000

"Hello" may be right justified red text on green background.
"World" may be left justified, bold text and underlined.
"10" might have a comment within the cell above.
etc

I will try stripping the formats in the value column to see clock
consumption.

Thanks for your reply.


Dave


"jmac" wrote in message
om...
First do a timing test to see just how much time is used in
maintaining the formatting. If it's a large enough percentage of the
overall time, consider pre-formatting your destination sheet.
To reduce the overall time of the process, precede the data collection
loop with Application.ScreenUpdating = False
Then after all the collection and pasting of data turn on
ScreenUpdating.
You could time the process with and without ScreenUpdating turned on
and with and without maintaining the cell formatting, just to identify
the time-intensive portion of your macro.

jmac