Extremely slow copy paste (cause known, solution not)

Hello everyone,

I wrote a code last week that copies values from a row in one work sheet into a range of another work sheet (C3:L103). The code itself works very well.
However, it is very slow in my work book and it takes 4 to 5 minutes to copy all the values.

After some testing I found out that it is not the code but it is related to the work book itself. The sheet I am copying into is a form that contains about 20 images on the right hand side that change according to values in drop down menus.

Formulas for this are stored in the name manager and they are all similar to this one:

OFFSET('Drawing options'!$E$8,MATCH(Cover!$D$20,'Drawing options'!$D$8:$D$10,0)-1,0)

Then the images refer to the formulas with their names.I am fairly certain this causes the copying to be slow as I removed sheets from the workbook one by one.

Has anyone ever run into this problem or has a potential solution? Would there be a way to use VBA to wait with calling the formulas for image changes until all values are copied? Is it better to change images with in an alternative way? Perhaps other solutions?

Suggestions are very much welcome.

Originally Posted by amros

Hello everyone,

I wrote a code last week that copies values from a row in one work sheet into a range of another work sheet (C3:L103). The code itself works very well.

Did you really?

Please Login or Register  to view this content.

Yes Jindon, with your great help This part mostly came from you but there's more than your input.

Thanks again. I will try this later today and report back here.

This made it faster but it still takes about a minute to copy the values.

When I copy and paste this range manually it takes less than 2 seconds to show the right images.

I will try to replace the OFFSET functions by somethings else. In the meantime, I would love to hear it if anyone has other suggestions.

I tried that one as well after searching on Google. Didn't make a big difference unfortunately.
First I will spend more time on the problem. If I can't get it to run faster, I will upload a sample file.

Solved.

Maybe not the most elegant solution but this is what I did:

I gave the name reference a formula that the reference is either empty or the offset function depending on a cell value in a hidden tab
(which is there already anyway) that can be TRUE or FALSE. It's TRUE by default. Beginning of macro I set this to false. The true and false are use in an if statement.