Very long macro - Thanks

Hi All

I used excel for year but only to do the simple things. I'm now getting into macro's, i have this macro that works but it takes an age to run and the screen flickering does my head in.
I have a sheet call Bene, which in col BO with have CAT , A,B,C,D,E,F,G or H. It will then move all the A to Bene Cat A.

I have only put this one on as an example but i have the same thing for UK,IBE,FRA,FIN
and all have 8 sheets to go with them. I have used the same code for all macros and i've even done a macro that does them all together using the same code.

Can someone please help me in making my macro more efficent

Please Login or Register  to view this content.

Hi,

You'd be better advised to upload the workbook and some notes as to exactly what sheets you are wanting to use and what you're trying to achieve as output.

It certainly looks as though the code could be simplified immensely. It may not even be necessary to use a loop to progress through cells in turn, a filtering function may be more appropriate. However even if a loop is required cutting out all the .Select and .Activate instructions, which I suspect are not needed, will speed up the code as well as switching off screen updating with Application.ScreenUpdating = False and probably temporarily putting the sheet(s) into manual calculation mode.

Regards

The number one tip I learned form this forum:
Dont use ".Select". Change all the ".Select" to ".Activate"

As an example: from your macro in the first post

Please Login or Register  to view this content.

I tried running your macro but could not get past:

Please Login or Register  to view this content.

It generates an error on Excel 2k.

Hi,

As I thought this could be simplified to a few lines of code using a filter rather than looping through cells.

First name your data range on the BENE sheet with the name 'Data' defined as:

Please Login or Register  to view this content.

Now use the following macro.

Please Login or Register  to view this content.

HTH

This the macro I would use, a slight tweak to Richard's:

Please Login or Register  to view this content.

Hi,

Or a 3rd approach where you don't need to name the range data and don't need to start with all the 8 CAT worksheets just the single BENE sheet. i.e. in a loop copy the BENE sheet each time and delete the non relevant rows for the other categories.

Please Login or Register  to view this content.

I'm intrigued though as to why you need to create separate sheets in the first place when you could just have a macro that filtered the BENE sheet in response to an input.

Regards

Comments added above into original macro.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)