Most Efficient Way To Pull Data From Multiple Files

Hi,

I currently have a Macro that pulls the data from work books in a file.

The only issue is that it runs very slowly. See Excerpt below there is a lot more data it copies.

Please Login or Register  to view this content.

The reason for the Else if, is because the data in the file could shift down depending on the inputs. So if there is text in cell that determines what data needs to be picked up.

I've also reduced the number of inputs here. There is 4 Else if statements and also about 170 cells I am picking the info from and pasting into a summary sheet.

Any help would be greatly appreciated.

Thanks and regards
Christian

Hi there,

See if the following code gets you moving in the right direction - instead of performing Copy / PasteSpecial operations it takes the values in the range to be copied and assigns them to an array, and then assigns the values in the array to the range into which the PasteSpecial operation would have been performed. In general this is a more efficient means of copying data.

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

Thanks so much for the help worked brilliantly.

just one question why do you use:

vbNullString instead of ""

Thanks
Christian

Hi again Christian,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome, I'm glad that I was able to help.

Using vbNullString instead of "" is just a personal preference. In general I try to minimise the number of " characters in the body of my routines (unless it relates to something like the text to be displayed in a message or prompt) - instead I usually define text values (e.g. "B23:E30") as constants at the start of a routine. There's nothing sacred about this - I just find it to be a bit more convenient when checking my code.

Best regards,

Greg M