If / Then Code Running Very Slow

[tswood]

Hi,

I'm working on VBA code to achieve the following:

- In Worksheet "One", search a column for values that are greater than specified
- If the criteria is met, then copy columns 2 and 4 to destination specified in Worksheet "MASTER"

The issue I am having is that the code takes a very long time to execute because there are so many rows to check in Worksheet "One" (over 12K)

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

Any pointers would be greatly appreciated

Please Login or Register  to view this content.

[vcoolio]

Hello TSWood,

As far as I can tell, you are searching Column P of the "One" sheet for any value greater than that specified in cell Y1 of the "Master" sheet and then transferring the corresponding values from Columns B and D of the "One" sheet to Columns A and F in the "Master" sheet.

If I've understood this correctly, then the following may help to speed it up a little for you:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

[Marc L]

Hi !

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 …

[Kenneth Hobson]

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.

[Marc L]

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 …

[Alf]

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

Please Login or Register  to view this content.

Alf

[vcoolio]

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.

[Marc L]

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.

[rorya]

Advanced filter copies only to the filter range's sheet.

FYI, that isn't correct. The source range, criteria range and target ranges can all be on any sheet you like. They don't even have to be in the same workbook.

[Kenneth Hobson]

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.

[rorya]

Actually, the rule is that the CopyTo range MUST be in the activesheet.

What do you base that on? There is no such requirement that I know of.

[tswood]

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.

[rorya]

I can't see anything there that could clear data. Are you sure it's not just hidden by a filter? Posting a workbook would be very helpful.

[tswood]

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.