If / Then Code Running Very Slow

Hi !

Originally Posted by tswood

Is there a way for me to make the code below more efficient?

An easy & faster way : an advanced filter needing a single codeline to filter & copy …

Welcome to the forum! Good job posting code and explaining what you need.

Advanced filter copies only to the filter range's sheet. I guess one could manually move the two columns after the filter or make code setup the criterion range and conditions, copy/paste, and delete copied to columns. I showed the manual way in the attachment. See AA1 and right in sheet One.

I would use autofilter somewhat similar to vcoolio's. I just tweaked his code a bit. Always test code on a backup copy. I also attached my simple file if you want to play with it.

Please Login or Register  to view this content.

As the initial post asked for copy and not for move, the reason why I talked about an advanced filter …
It just needs the same headers spelling in the destination worksheet and a criteria range.

If only an attachment with a before context and an expected result …

Modifying the OP's original code a bit I think this should work.

Please Login or Register  to view this content.

Alf

Hi Kenneth,

In your tweaked code:

x As Integer

Thanks for the prompt! I should probably get back into the habit of heading the code with "Option Explicit".

@Marc L:-

I agree with Kenneth.

As the initial post asked for copy and not for move

If you had carefully read the OP's opening post, including the code supplied, you would have realised that he is wanting to copy and paste. Is this not transferring (or moving) data?

Cheerio to all.
vcoolio.

As 'moving' sounds like cut / paste instead of copy / paste like under the files explorer : do you move or just copy a file ? …

If only an attachment with the expected result but for general purpose like filtering & copy the advanced filter may well do the job.

Actually, the rule is that the CopyTo range MUST be in the activesheet. Obviously, the other two inputs can be on any sheet.

Here is how to do it with AdvancedFilter named sheet as activesheet. If one were to run the code from another sheet, it would fail unless the copyto sheet was activated by the code before the filter. With the button on the sheet, it is already activated.

Please Login or Register  to view this content.

I modified the file to show both methods.

Hi All,

Thank you for your help with the code, I very much appreciate it. Just to clarify, I want to 'copy' not 'move' the data.

I've tried a variation of the code provided (adding a few additional columns to copy and paste) but am not having much luck.

Currently, the code clears all data from ws1/ Sheets("One") from row 2 down. This includes columns not referred to in the code.
Secondly, the data is not appearing in ws/ Sheets("Master"). This remains blank.

Here is the code I am using. Have I perhaps typed something incorrectly or am I missing a crucial part?

Please Login or Register  to view this content.

Hi rorya - you are right, yes. The data is hidden not cleared. Is there a way for me to stop this?

The data still isn't coping to the new sheet though.

Unfortunately I can't post the workbook I'm using but can try creating something similar.