excel is slow when running macro on filtered cells

hello


i have this code which copies some columns from "temp file" to "target file".

macro is working ok when there are no filtered cells in the "source file" but when i filtered 1 column in the "source file" and run the macro... my excel runs slowly. it still copies only the visible cells into the target file, but my problem is it is running very slow.

i hope somebody can take a look at my code below and advise what is wrong. i wish it would run as fast as when there are no filtered cells in my temp file. note i know my code has a lot of errors, i am a newbie in macro so i only recorded it... :P if somebody can give me a better macro, i appreciate it...

-----

Please Login or Register  to view this content.

Hi, melody10,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

Hi, Melody,

there is no indication about any filter in the code. I assume you might use

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Maybe even add Application.ScreenUpdating ?= False at the start of the procedure and ~ = True at the end.

Untested as CloseWorkbook isnīt available:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, Melody,

you should work on a couple of the commands as it it a bit different to copy whole columns as opposed to just the filtered areas. I assumed that the data should be inserted after the formulas (so the range to copy to starts in Row 2001):

Please Login or Register  to view this content.

It took you more than four hours to answer but you want us to answer within less than half an hour? Maybe consider that itīs Saturday afternoon local time for me and somebody may have other/more important things to do than answer in an ExcelForum and that everybody here is in it for free. And I personally dislike posting code which is untested or may cause problems so Iīm trying to check the code thoroughly before posting (Iīm not failsafe to errors as you may have noticed with my first code and the testing takes itīs time). Maybe the code I posted here will raise errors when you run it on your machine - it didnīt do so on my side.

Ciao,
Holger

Hi, melody10,

from what you say I understand that oyu placed the code in Temp... while I would have expected it to be in Cargo... - my fault due to misunderstanding.

Please alter

Please Login or Register  to view this content.

to read

Please Login or Register  to view this content.

The difference in the process is that you copy more than a million rows which are visible by selecting whole columns while I try to cut the amount down to just 2 rows as are visible in your sample. Next difference may be the next misunderstanding - you replace everything when pasting into the target workbook while I try to amend the data under what is present in the workbook.

Ciao,
Holger

Hi, melody10,

one problem... data should be pasted starting in row 8 of cargo readiness file... not in row 2001...

Not really a problem.

My code was

Please Login or Register  to view this content.

which looks for the first available cell in Column E. You can use

Please Login or Register  to view this content.

instead but you might consider to delete any contents in the target sheet prior to copying because if you had copied three lines last time and there will be only two rows in the present process the third line would remain and might lead to unwanted action or confusion.

please be patient with me...

Iīm sorry if anything I wrote has sounded like being impatient - I can assure you I didnīt mean it that way.

HTH,
Holger