Faster code

[imback2nite]

Thank you in advance for any help. I'm using the below vba script. I have a calendar where if the =sumif is satisfied, the row below is calculated. There are 21 rows and 24 sets of criteria. I'm trying to input these formulas in the range cells when the page is activated. Then when the page is deactivated I would like the range cells to become values. There are 40 pages. This is why I would like to limit the number of formulas per page. Also the script 'pauses' and needs to work faster. That is if it's worth doing. I know it's alot going on. Help would be appreciated greatly!

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("J3") = ("=SUMIF(B11:AS11,Sheet1!$A$1,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$1,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$1,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$1,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$1,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$1,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$1,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$1,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$1,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$1,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$1,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$1,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$1,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$1,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$1,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$1,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$1,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$1,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$1,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$1,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$1,B81:AS81)")
Range("J4") = ("=SUMIF(B11:AS11,Sheet1!$A$2,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$2,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$2,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$2,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$2,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$2,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$2,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$2,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$2,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$2,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$2,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$2,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$2,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$2,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$2,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$2,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$2,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$2,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$2,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$2,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$2,B81:AS81)")
Range("J5") = ("=SUMIF(B11:AS11,Sheet1!$A$3,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$3,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$3,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$3,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$3,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$3,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$3,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$3,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$3,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$3,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$3,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$3,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$3,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$3,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$3,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$3,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$3,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$3,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$3,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$3,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$3,B81:AS81)")
Range("J6") = ("=SUMIF(B11:AS11,Sheet1!$A$4,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$4,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$4,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$4,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$4,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$4,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$4,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$4,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$4,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$4,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$4,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$4,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$4,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$4,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$4,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$4,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$4,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$4,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$4,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$4,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$4,B81:AS81)")
Range("J7") = ("=SUMIF(B11:AS11,Sheet1!$A$5,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$5,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$5,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$5,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$5,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$5,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$5,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$5,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$5,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$5,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$5,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$5,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$5,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$5,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$5,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$5,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$5,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$5,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$5,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$5,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$5,B81:AS81)")
Range("J8") = ("=SUMIF(B11:AS11,Sheet1!$A$6,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$6,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$6,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$6,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$6,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$6,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$6,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$6,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$6,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$6,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$6,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$6,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$6,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$6,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$6,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$6,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$6,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$6,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$6,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$6,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$6,B81:AS81)")
Range("R3") = ("=SUMIF(B11:AS11,Sheet1!$A$7,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$7,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$7,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$7,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$7,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$7,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$7,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$7,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$7,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$7,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$7,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$7,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$7,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$7,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$7,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$7,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$7,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$7,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$7,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$7,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$7,B81:AS81)")
Range("R4") = ("=SUMIF(B11:AS11,Sheet1!$A$8,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$8,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$8,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$8,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$8,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$8,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$8,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$8,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$8,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$8,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$8,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$8,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$8,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$8,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$8,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$8,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$8,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$8,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$8,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$8,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$8,B81:AS81)")
Range("R5") = ("=SUMIF(B11:AS11,Sheet1!$A$9,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$9,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$9,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$9,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$9,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$9,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$9,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$9,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$9,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$9,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$9,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$9,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$9,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$9,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$9,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$9,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$9,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$9,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$9,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$9,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$9,B81:AS81)")
Range("R6") = ("=SUMIF(B11:AS11,Sheet1!$A$10,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$10,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$10,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$10,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$10,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$10,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$10,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$10,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$10,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$10,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$10,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$10,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$10,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$10,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$10,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$10,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$10,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$10,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$10,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$10,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$10,B81:AS81)")
Range("R7") = ("=SUMIF(B11:AS11,Sheet1!$A$11,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$11,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$11,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$11,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$11,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$11,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$11,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$11,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$11,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$11,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$11,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$11,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$11,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$11,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$11,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$11,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$11,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$11,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$11,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$11,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$11,B81:AS81)")
Range("R8") = ("=SUMIF(B11:AS11,Sheet1!$A$12,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$12,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$12,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$12,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$12,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$12,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$12,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$12,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$12,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$12,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$12,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$12,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$12,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$12,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$12,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$12,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$12,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$12,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$12,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$12,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$12,B81:AS81)")
Range("AA3") = ("=SUMIF(B11:AS11,Sheet1!$A$13,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$13,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$13,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$13,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$13,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$13,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$13,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$13,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$13,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$13,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$13,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$13,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$13,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$13,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$13,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$13,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$13,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$13,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$13,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$13,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$13,B81:AS81)")
Range("AA4") = ("=SUMIF(B11:AS11,Sheet1!$A$14,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$14,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$14,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$14,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$14,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$14,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$14,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$14,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$14,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$14,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$14,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$14,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$14,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$14,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$14,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$14,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$14,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$14,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$14,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$14,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$14,B81:AS81)")
Range("AA5") = ("=SUMIF(B11:AS11,Sheet1!$A$15,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$15,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$15,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$15,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$15,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$15,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$15,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$15,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$15,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$15,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$15,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$15,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$15,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$15,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$15,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$15,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$15,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$15,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$15,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$15,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$15,B81:AS81)")
Range("AA6") = ("=SUMIF(B11:AS11,Sheet1!$A$16,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$16,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$16,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$16,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$16,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$16,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$16,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$16,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$16,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$16,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$16,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$16,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$16,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$16,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$16,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$16,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$16,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$16,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$16,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$16,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$16,B81:AS81)")
Range("AA7") = ("=SUMIF(B11:AS11,Sheet1!$A$17,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$17,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$17,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$17,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$17,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$17,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$17,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$17,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$17,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$17,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$17,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$17,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$17,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$17,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$17,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$17,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$17,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$17,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$17,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$17,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$17,B81:AS81)")
Range("AA8") = ("=SUMIF(B11:AS11,Sheet1!$A$18,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$18,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$18,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$18,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$18,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$18,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$18,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$18,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$18,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$18,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$18,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$18,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$18,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$18,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$18,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$18,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$18,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$18,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$18,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$18,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$18,B81:AS81)")
Range("AH3") = ("=SUMIF(B11:AS11,Sheet1!$A$19,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$19,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$19,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$19,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$19,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$19,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$19,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$19,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$19,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$19,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$19,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$19,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$19,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$19,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$19,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$19,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$19,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$19,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$19,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$19,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$19,B81:AS81)")
Range("AH4") = ("=SUMIF(B11:AS11,Sheet1!$A$20,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$20,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$20,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$20,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$20,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$20,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$20,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$20,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$20,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$20,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$20,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$20,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$20,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$20,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$20,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$20,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$20,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$20,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$20,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$20,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$20,B81:AS81)")
Range("AH5") = ("=SUMIF(B11:AS11,Sheet1!$A$21,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$21,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$21,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$21,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$21,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$21,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$21,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$21,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$21,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$21,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$21,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$21,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$21,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$21,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$21,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$21,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$21,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$21,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$21,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$21,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$21,B81:AS81)")
Range("AH6") = ("=SUMIF(B11:AS11,Sheet1!$A$22,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$22,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$22,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$22,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$22,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$22,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$22,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$22,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$22,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$22,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$22,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$22,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$22,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$22,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$22,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$22,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$22,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$22,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$22,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$22,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$22,B81:AS81)")
Range("AH7") = ("=SUMIF(B11:AS11,Sheet1!$A$23,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$23,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$23,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$23,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$23,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$23,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$23,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$23,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$23,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$23,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$23,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$23,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$23,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$23,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$23,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$23,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$23,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$23,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$23,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$23,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$23,B81:AS81)")
Range("AH8") = ("=SUMIF(B11:AS11,Sheet1!$A$24,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$24,B15:AS15)+SUMIF(B17:AS17,Sheet1!$A$24,B18:AS18)+SUMIF(B20:AS20,Sheet1!$A$24,B21:AS21)+SUMIF(B23:AS23,Sheet1!$A$24,B24:AS24)+SUMIF(B29:AS29,Sheet1!$A$24,B30:AS30)+SUMIF(B32:AS32,Sheet1!$A$24,B33:AS33)+SUMIF(B35:AS35,Sheet1!$A$24,B36:AS36)+SUMIF(B38:AS38,Sheet1!$A$24,B39:AS39)+SUMIF(B41:AS41,Sheet1!$A$24,B42:AS42)+SUMIF(B47:AS47,Sheet1!$A$24,B48:AS48)+SUMIF(B50:AS50,Sheet1!$A$24,B51:AS51)+SUMIF(B53:AS53,Sheet1!$A$24,B54:AS54)+SUMIF(B56:AS56,Sheet1!$A$24,B57:AS57)+SUMIF(B59:AS59,Sheet1!$A$24,B60:AS60)+SUMIF(B62:AS62,Sheet1!$A$24,B63:AS63)+SUMIF(B68:AS68,Sheet1!$A$24,B69:AS69)+SUMIF(B71:AS71,Sheet1!$A$24,B72:AS72)+SUMIF(B74:AS74,Sheet1!$A$24,B75:AS75)+SUMIF(B77:AS77,Sheet1!$A$24,B78:AS78)+SUMIF(B80:AS80,Sheet1!$A$24,B81:AS81)")
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub

 

[dave3009]

Hi imback2nite,

Far be it from me to decipher what your code is attempting to do, but from what you say you want these ranges to be set to values when the sheet is deactivated. The thing is when you come back and activate the page it will run again. In fact if you have 40 sheets, each with a similar activate event then you will probably spend a lot of time waiting for the code to execute. Then there is the issue of whether or not each time you switch sheets do your changed ranges interact with the other pages, this will fire off calculation which might hamper things.

As a suggestion, and seeing there is little other than a guess as to how your project is setup, why not turn off calculation while the code executes.

Application.Calculation = xlCalculationManual

' code goes here


Application.Calculation = xlCalculationAutomatic

 

[imback2nite]

Thank you for you Quick reply! It dropped the lag time to maybe four seconds. I'm rewriting the =SUMIF to
If Range("J3") <> ("=SUMIF(B11:AS11,Sheet1!$A$1,B12:AS12)+SUMIF(B14:AS14,Sheet1!$A$1,B15:AS15) etc. etc. etc.
This should drop the lag time even more. Thank you Dave!

 

[FDibbins]

You seem to have a heck of a lot of overlap in your SUMIF() ranges there...
'=SUMIF(B11:AS11,Sheet1!$A$1,B12:AS12)
+SUMIF(B14:AS14,Sheet1!$A$1,B15:AS15)
+SUMIF(B17:AS17,Sheet1!$A$1,B18:AS18)
+SUMIF(B2 0:AS20,Sheet1!$A$1,B21:AS21)
+SUMIF(B23:AS23,Sheet1!$A$1,B24:AS24)
+SUMIF(B29:AS29,Sheet1!$A$1,B30:AS30)
+SUMIF(B32:AS32,Sh eet1!$A$1,B33:AS33)

Something, for instance, in B33 will get added at least 7 times in just those few samples?

What exactly are you trying to do here?

 

[dave3009]

Right, try this on a COPY of your sheet. If it yields the same result we can look at the next step.

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
        With ActiveSheet
            .Unprotect
            .Range("J3:J8, R3:R8, AA3:AA8, AH3:AH8").Formula = "=SUMPRODUCT(--($B$10:$AS$80=Sheet1!A1), $B$11:$AS$81)"
            .Protect
        End With
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

 

[dave3009]

Apologies, I looked again and saw the column reference doesn't shift right, it just runs down Sheet1!A:A

Again in a COPY of your workbook.

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
        With ActiveSheet
            .Unprotect
            .Range("J3:J8").Formula = "=SUMPRODUCT(--($B$10:$AS$80=Sheet1!A1)*(MOD(ROW($B$9:$AS$79), 3)=1),$B$11:$AS$81)"
            .Range("R3:R8").Formula = "=SUMPRODUCT(--($B$10:$AS$80=Sheet1!A7)*(MOD(ROW($B$9:$AS$79), 3)=1),$B$11:$AS$81)"
            .Range("AA3:AA8").Formula = "=SUMPRODUCT(--($B$10:$AS$80=Sheet1!A13)*(MOD(ROW($B$9:$AS$79), 3)=1),$B$11:$AS$81)"
            .Range("AH3:AH8").Formula = "=SUMPRODUCT(--($B$10:$AS$80=Sheet1!A19)*(MOD(ROW($B$9:$AS$79), 3)=1),$B$11:$AS$81)"
            'With .Range("J3:J8, R3:R8, AA3:AA8, AH3:AH8")
            '    .Value = .Value
            'End With
            .Protect
        End With
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

 

[imback2nite]

Dave, thank you. This code works great! One thing. How do I have the active sheet receive the values? When I move to another page, the code works there, not on the page I’m changing from. Ie. If I’m on page 4, I want to make the ranges on THAT page reduced to values rather than formulas.

FDibbins, you are absolutely right. All the formulas are exactly the same except for the criteria in the SUMIF formulas. But, as you see, the Ranges are one row above the Sum_ranges. There are 21 instances of this as this is a calendar and I need sums if the criteria are satisfied.

 

[imback2nite]

I'm sorry. This is a totally different question but is there a way I can turn this into a VBA rather than a cell using a cell. =IF(COUNTIF(J3:K8,"=")+COUNTIF(R3:S8,"=")+COUNTIF(AA3:AB8,"=")+COUNTIF(AH3:AI8,"=")<24,"Run Code","Don't Do Anything.")
Right now I'm using the below code but I have the formula in cell ("AU5")

Private Sub Worksheet_Activate()
If Range("AU5") < 24 Then
Application.Run "Update_Emp_Info"
Else
End If
End Sub

 

[dave3009]

Hi

First question - how do you change it from the active sheet?

Change:

With Activesheet

To:

With Sheets("Sheets Name Here")

Change the sheets name to the desired sheet name

I'm not entirely clear on the second question, when do you want the change to happen? Is it as and when required or just as soon as AU5 < 24?

 

[dave3009]

Your function

=IF(COUNTIF(J3:K8,"=")+COUNTIF(R3:S8,"=")+COUNTIF(AA3:AB8,"=")+COUNTIF(AH3:AI8,"=")<24,"Run Code","Don't Do Anything.")

Can be written like this

=IF(SUM(COUNTIF(INDIRECT({"J3:K8","R3:S8","AA3:AB8","AH3:AI8"}),"="))<24, "Run Code","Don't Do Anything")

So it's just a question of when do you want the code to run, is it just as and when required or as soon as the empty cells in these ranges are blank?