VBA code .range problem

[steve400243]

Hello, On the attached spreadsheet when the data in column A, Active Opportunities changes to "Closed" it automatically moves to the "Closed Business" sheet, but it deletes other data that i need to keep past column Q. Any ideas on how to modify the code to fix this? I'm not sure how to change the range. Thanks in advance.

[karedog]

Maybe :

Please Login or Register  to view this content.

[steve400243]

Awesome Karedog, Works just like i need it to. I really appreciate it!

[karedog]

You are welcome Steve, thanks for marking the thread as solved and for the rep.points.


Regards

[steve400243]

No Problem, Thank you. One other quick question if I could? In column "m" do you know why the cells in red display that number " of 30483"?? i would like it to be blank until the dates are put in. Thanks for your help.

[karedog]

You can put this formula on cell M4 (and copied down) :

Please Login or Register  to view this content.

As for why it is returning 30483 if adjacent cell in column L is blank, the explanation is :
- NETWORKDAYS(start_date,end_date,holidays) function return number of working days (5 days per week, Monday - Friday) between start date and end date, excluding holidays
- If adjacent cell in column L is blank, this means start_date = 0
- The value of end_date ($P$1) is 42681, you can examine this by change number format of cell $P$1 to General
- The number holidays to be subtracted between start_date (0) and end_date (=TODAY()) is 3 days, defined on Data!$P$2:$P$11, that is : 04/07/2016, 05/09/2016, and 10/10/2016
- So the days between end_date (426812) and start_date (0), excluding holidays is = 42681 -0 - 3 = 42678
- Approx. of network days (5 days a week --> 5 / 7) = 5/7 * 42678 = 30484 --> (this is very close to 30483, it must be caused by rough calculation by very simple multiplying with 5/7)

[steve400243]

Thank you again, I appreciate the explanation. Have not used the network day formulas that much. Have a good evening.

[karedog]

You are welcome Steve, it is morning here in Indonesia. Have a wonderful evening.


Regards