Current code to cycle through sheets and copy values is very slow - can it be sped up?

[maym]

Hi

I have the following code that cycles through my workbook (currently has about 150 sheets so is very large) and copies certain values to my main sheet. It is extremely slow.

I am wondering if there is a better and faster way to do this? I also update my workbook frequently so when I add a sheet, I need to run the code all over again, which is very frustrating. I am looking for a way to also just update based on the sheet I have just added?

This is just one part of the code - it is repeated three more times however I cannot post here as it is too long!

Please Login or Register  to view this content.

[gmr4evr1]

Change this at the beginning of your code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then add this just before the End Sub line

Please Login or Register  to view this content.

This should (in theory) speed it up a bit.

[xladept]

I don't think it can be "sped-up" - maybe:

Please Login or Register  to view this content.

[maym]

I tried this code xladept but I can an error here:

Please Login or Register  to view this content.

The error is 'subscript out of range'?

[bakerman2]

Change this.

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

[maym]

thanks bakerman but now I get this error down the bottom:

Please Login or Register  to view this content.

which is: 'Next without for'

I assume I need to re-order these at the end somehow?

[bakerman2]

You'll have to indent your code properly and find out where there's missing or misplaced something.

[maym]

well, I have taken xladept's suggestion above so I am not exactly sure what's missing?

[xladept]

I went line by line through your code, unless I missed something the change should have worked

But, with Bakerman's rearrangement the End With has to precede the Next j.

I've edited the post

[maym]

with the changes made, it appears to be working now. I will do some more testing.

Thanks for your help

[xladept]

But, is it any quicker?

[maym]

sorry, yes it is! About 3 times faster, so really appreciate your help thank you

[xladept]

Good to know.