Speed up my workbook

[Bukol]

Hi there
I am using Excel 2000.
I created a workbook with several macros which do the following:
From sheet 1, using a web query it extracts a table of data from an external website and puts it in a range of cells on sheet 1.
It then copies one by one 9 different cells from that range and pastes it into a different order on sheet 3.
Then using a list of keywords stored on sheet 2, it repeats the procedure but going to a different page on the website.
There are alltogether 26 cut or copy or paste actions involved in one run, which apparently slows everything down.
When I first started to run that thing, it extracted about 40 sets of data a minute. Then, after about 1000 runs, it slowed down to 1 set in 3 minutes!
I changed the code by adding a macro to cut the accumulated data on sheet 3 and pasted it into a second workbook. It helped a little, now I extract about 3 sets per minute. The speed is now constant. But not good enough. I have to extract 130000 sets of data, that would take me about a month of permanent running to extract them all!!!
Any idea how to speed that thing up?
If needed I would send you the complete code.
Hans

 

[Jack in the UK]

Hi

Can i add, i am reluctant to post any VBA i write, but please can i ask you look at Dave one liner.. this thinking i follow and dam thats good.. i suggest copy and paste into a holding file, else you have a page of code to do this, i see this TIME nad TIME again, not that its an error just an approach,

Ferrari are not that fast against BMW Williams, but Arrows are the fastest not many know this Jaguar are also VERY fast, who is the best...


Depends on the day and what you need fast is not always fastest but this code will sort many problems. A good habbit.