Code needs cleaning up

**hambone** · 07-08-2010, 12:01 PM

The below code is something I've inherited and I'm pretty sure it's a complete mess. I would really like to clean it up by removing anything unecessary or redundant. My ultimate goal is to make this code much shorter and more efficient. But alas, I'm a novice with this kind of stuff.

Any help is greatly appreciated.

Please Login or Register  to view this content.

Thanks guys

**Marcol** · 07-08-2010, 12:34 PM

Try this to start with
Check it does the same, I might have got a bit lost.

[EDIT]
code revisited after comments in Post #3

Please Login or Register  to view this content.

Check your formulae, I can't confirm they do as you want.
Also
Check your sort criteria

Hope this helps

**hambone** · 07-08-2010, 01:04 PM

Thanks Marcol. I appreciate your help.

When I run your macro, a couple of things happen:

The second line of the code (Range("G2").....) pastes the values in Sheet 2, which is where a form button is located with this macro assigned to it.

Also, as the macro is running, it tries to open a file three different times and I'm afraid I don't have any idea what would cause that.

Again, thanks for your help.

**Marcol** · 07-08-2010, 01:13 PM

Got to break a while I'll check later.

**Marcol** · 07-09-2010, 10:51 AM

1/.

The second line of the code (Range("G2").....) pastes the values in Sheet 2, which is where a form button is located with this macro assigned to it.

That's where you ask for it to go! Shift the button, or change the destination cell.

2/.

as the macro is running, it tries to open a file three different times

Check the number of times your formulae calls 'Sheet 1' instead of 'Sheet1', this looks like the source of this error.

Rather than clutter the BB with useless code, I have edited Post #2.
I have corrected what I see as errors, and simplified as much as I can confidentlly can.

Check that revised code out

Hope this helps