speeding up copy and paste code

[cereldine]

Im developing some code that will look for values in about 1
spreadsheets that are provided for us externally (sheet A).
I need to extract about 115 columns of data out of about 123 b
determining the criteria, i can do this fine, i can also declare th
range i want to copy fine and if this code is run on its own it wil
loop through to the end in seconds.

The code starts to slow down when i start adding the code to paste th
data into the required spreadsheet (sheet B), this has to be done on
coloumn at a time so as to ommit columns i don't need. the copy cod
and paste code work really fast, the code slows down when iv
performed one iteration and am reopening/activating sheet A afte
pasteing in sheet b?

I can't understand what the problem is, basically if i choose to
remove the copy and paste code and just have the sheets open in th
loop 123 times it still goes at snails pace ie 5 seconds a loop! It
not the sheets that are a problem as i recorded a macro to test thi
problem and it runs at the normal speed that i desire.

ive been opening workbooks with following
Windows("intermediate.xls").Activate OR
bkI.Activate - where bki is a declared workbook

Any idea

 

[Jim Cone]

Sounds like you could copy the entire sheet (once) and then
delete the columns you don't need from the copied sheet.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"cereldine"
wrote in message
Im developing some code that will look for values in about 15
spreadsheets that are provided for us externally (sheet A).
I need to extract about 115 columns of data out of about 123 by
determining the criteria, i can do this fine, i can also declare the
range i want to copy fine and if this code is run on its own it will
loop through to the end in seconds.

The code starts to slow down when i start adding the code to paste the
data into the required spreadsheet (sheet B), this has to be done one
coloumn at a time so as to ommit columns i don't need. the copy code
and paste code work really fast, the code slows down when ive
performed one iteration and am reopening/activating sheet A after
pasteing in sheet b?

I can't understand what the problem is, basically if i choose to i
remove the copy and paste code and just have the sheets open in the
loop 123 times it still goes at snails pace ie 5 seconds a loop! Its
not the sheets that are a problem as i recorded a macro to test this
problem and it runs at the normal speed that i desire.

ive been opening workbooks with following
Windows("intermediate.xls").Activate OR
bkI.Activate - where bki is a declared workbook

Any ideas

 

[cereldine]

Ive done some further testing and come up with some strange results tha
i cannot understand, basically i created the below statement to ts
where the proble lay

Dim i As Integer
Workbooks.Open ("S:\intemediate.xls")
Workbooks.Open ("S:\supply.xls")

Do
i = 0 + i
Windows("intermediate.xls").Activate
Windows("Supply.xls").Activate
i = i + 1
Loop Until i = 100

This code ran at snails pace again! However if i highlight out th
workbooks.open statements and have the workbooks already open then th
loop runs a 100 times in about 2 seconds,

Can anyone explain why this is as it could have an overall bearing o
the way i'm approaching the proble