for each loop dosn't work

[rhamid6]

Hi guys

I have a private sub which calls my macro when any cell within a row (A1 tp end Right) changes to "Delivered" . then Macro2 copies the entire related column and paste the value in another sheet. for some reason Macro2 does not work. It doesn't even give me an error so I can work on. I will appreciate if you can guide me on this

Please Login or Register  to view this content.

------------

Please Login or Register  to view this content.

Thank you for any help in advance

[SjonR]

why two different subs to do the job? try it please.

Please Login or Register  to view this content.

[rhamid6]

Thank you so much for your answer. That worked very well. Instead of copying and pasting entire related column, is it possible to copy and paste only a few cells of that column? I want to say If the word "Delivered" was found copy only some specific cells of that column and paste it as special. (cells rows (3 to 10), related columns) and cells (rows(16 to 20), related column).

Please Login or Register  to view this content.

for the first part I tried that but it gives me an error that the method is not supported. Thank you for you help

[Richard Buttrey]

This question crops up time and time again and if we had a FAQ area that dealt with Excel stuff rather than forum stff it would be my numero uno.

Whenever you find yourself writing looping code, stop, take a breath and think if there isn't a better way. Using loops to do this sort of stuff, particularly when you are processing large ranges is inherently slow because of the need to jump back and forth between VBA and the Excel app., which carries a big time overhead.

The fastest and most elegant and efficient way I know to do this is by filtering.

Identify the column that contains the values you want to use for selecting rows - presumably in your case the column with the "7 Delivered" text.
AutoFilter your range to display these rows.
Then use
Range("your_big_range").SpecialCells(xlCellTypeVisible).Copy

and then paste them as values to your destination, finally removing the Auofilter.