Slow Macro

[abz]

Hi guys,

i have just recorded a macro to copy a cell from one sheet to another based on a filter criterion, the code is as below, is it possible to speed this macro up?. i mean currently its just going through 6 different values for x, but when running properly it will be going through around 150 or somewhere around those lines, so i am guessing it will be annoying and pretty time consuming.

Please Login or Register  to view this content.

Just to make this a bit easier i am also attaching the worksheet i have been working on.

Let me know if you have any suggestions of how to do this best and most efficiently.

Thanks,
Abz

[abz]

Just to add, if i dont have this as a macro and rather have it as a Worksheet_change by value or something else, will that help to make this more efficient?

[royUK]

Your code is slow because you select and activate ranges & sheets. Your suggestions in the second post would have no effect at all on efficiency.

Please Login or Register  to view this content.

[abz]

Please Login or Register  to view this content.

Roy,

Thanks a lot for the suggestion, it deifnately works a lot quicker than mine, i knew the select bit was slowing it down but i tried omiting it but it wasn't working in the first place as i wasn't using the correct code lines.

Although i just had to make a slight change maybe you js forgot to add it, a '.' before the above code line and the brackets for the 'copy' command.

Anyways thanks again for that.

One thing, is it possible to do this same thing with a combo of the few builtin formulae avaiblable in excel?, becuase that wud js make it a lot quicker and i wouldnt have to runa macro for this. Basically what it does is takes a value that is in a table in sheet 1 and searches it in a database in sheet 4 and then returns the latest record in sheet 1 according to the dates. So is there any better way of doing this task instead of using a macro?

Regards,

Abz

[royUK]

You don't need brackets around the copy command