Copy/Paste Macro Bugs the File

[Olibouch]

Hi,

I have a copy/paste VBA script in my workbook but it completly bug my file when it's executed.

Here is a link to my workbook.

https://drive.google.com/file/d/0B6a...it?usp=sharing

The bug appears when I select "Q1,Q2,Q3,Q4 or Total" in my sheet "Tableau des défauts".

I use Excel 2007 and my computer has a strong enough processor.

Olivier

[Bernie Deitrick]

Any event code should have appropriate Error Handling:

First line:

On Error Goto ErrHandler:

'Other code

'Last lines
ErrHandler:
Application.EnableEvents = True
End Sub


And should start with the second line:

Application.EnableEvents = False

and make sure that before any Exit Sub lines you use

Application.EnableEvents = True

Otherwise, you risk getting into infinite loops that will bog down even the most powerful machines

And if you have event code, you may want to use the same logic in other macros - depends on what you want to have happen....

[Olibouch]

Thank you for your answer Bernie. I made the corrections but I'm still stuck in an infinite loop. Here is the new code:

Please Login or Register  to view this content.

[Bernie Deitrick]

You're doing way too much looping. Try changing (typical, for all your loops):

Please Login or Register  to view this content.

to just this, to copy the cells as blocks (I think the A range should be copied to ws as well.....)

Please Login or Register  to view this content.

[Olibouch]

The code seems well written but when I try it, nothing is copied. I don't have my bug but it still doesn't work properly. I also tested to put my code in a button and it's the same infinite loop.

[Bernie Deitrick]

Your code only copies from Sheets "Avril", "Mai", and "Juin", no matter what is chosen. And you did not make the latest changes, anywhere. So the problem is in .... a lot of things, probably. Change the loops as indicated in my last message, and change the sheet names to the correct sheets based on the value selected in E2, then post back.

[Olibouch]

It's because I didn't uploaded the latest version of the file. Here is the link to the new file:

https://drive.google.com/file/d/0B6a...it?usp=sharing

[Bernie Deitrick]

Well, your code worked, but was a little off in clearing the used cells on Calcus - you had data starting in Row 7001, so the new data was being pasted way, way down the sheet. to fix that, change all the 7000s to 70000 or 700000, or even 1048576

Also, you have a LOT of calculations, so for all your events, use code like this at the top to prevent recalculation with every paste:

Please Login or Register  to view this content.

And then finish with

Please Login or Register  to view this content.

[Olibouch]

Thank you for the time you passed on my problem.