5
\$\begingroup\$

From the requests below I've attempted to rewrite my request. I agree completely that it would be easier for our company to provide us with help desk software. Unfortunately, my department has the assignment to sit between our end users and three separate help desks that deal with different problems. We are not allowed to enter in any help desk tickets until we have determined which of the help desk systems the issue belongs in, and many of the issues we handle are never taken to the help desk because we are able to resolve them through education. Our department manager has been working for years trying to get us access to a help desk with no result, so instead of waiting for the company to finally do something I've been working to build this file to give our team something we can use.

I have taken a copy of my file and manipulated it into a testing file which contains every cell that is affected by the VBA code but also meets our company's data security policies. I have included screenshots and the troublesome pieces of code below. Please let me know if there is any more information I can provide about the file.

Here is a picture of the priority list screen:

enter image description here

and here is an image of the table where the issues are stored:

enter image description here

The issue with the code that causes me the most trouble d/t bulk is the code that allows the users to freely update the issues from the first screen, and have that information directly updated on the second screen. The code functions completely but is very 'fat' so any help on slimming it down would be greatly appreciated.

The code for that process is:

Private Sub Worksheet_Change(ByVal Target As Range)

Set r1 = Sheets("Priority Table").Range("B2")
Set r2 = Sheets("Priority Table").Range("I2")
Set r3 = Sheets("Priority Table").Range("P2")
Set r4 = Sheets("Priority Table").Range("B13")
Set r5 = Sheets("Priority Table").Range("I13")
Set r6 = Sheets("Priority Table").Range("P13")
Set r7 = Sheets("Priority Table").Range("B24")
Set r8 = Sheets("Priority Table").Range("I24")
Set r9 = Sheets("Priority Table").Range("P24")
Set r10 = Sheets("Priority Table").Range("B35")
Set r11 = Sheets("Priority Table").Range("B4")
Set r12 = Sheets("Priority Table").Range("I4")
Set r13 = Sheets("Priority Table").Range("P4")
Set r14 = Sheets("Priority Table").Range("B15")
Set r15 = Sheets("Priority Table").Range("I15")
Set r16 = Sheets("Priority Table").Range("P15")
Set r17 = Sheets("Priority Table").Range("B26")
Set r18 = Sheets("Priority Table").Range("I26")
Set r19 = Sheets("Priority Table").Range("P26")
Set r20 = Sheets("Priority Table").Range("B37")
Set r21 = Sheets("Priority Table").Range("D4")
Set r22 = Sheets("Priority Table").Range("K4")
Set r23 = Sheets("Priority Table").Range("R4")
Set r24 = Sheets("Priority Table").Range("D15")
Set r25 = Sheets("Priority Table").Range("K15")
Set r26 = Sheets("Priority Table").Range("R15")
Set r27 = Sheets("Priority Table").Range("D26")
Set r28 = Sheets("Priority Table").Range("K26")
Set r29 = Sheets("Priority Table").Range("R26")
Set r30 = Sheets("Priority Table").Range("D37")
Set r31 = Sheets("Priority Table").Range("C8")
Set r32 = Sheets("Priority Table").Range("J8")
Set r33 = Sheets("Priority Table").Range("Q8")
Set r34 = Sheets("Priority Table").Range("C19")
Set r35 = Sheets("Priority Table").Range("J19")
Set r36 = Sheets("Priority Table").Range("Q19")
Set r37 = Sheets("Priority Table").Range("C30")
Set r38 = Sheets("Priority Table").Range("J30")
Set r39 = Sheets("Priority Table").Range("Q30")
Set r40 = Sheets("Priority Table").Range("C41")
Set r41 = Sheets("Priority Table").Range("C9")
Set r42 = Sheets("Priority Table").Range("J9")
Set r43 = Sheets("Priority Table").Range("Q9")
Set r44 = Sheets("Priority Table").Range("C20")
Set r45 = Sheets("Priority Table").Range("J20")
Set r46 = Sheets("Priority Table").Range("Q20")
Set r47 = Sheets("Priority Table").Range("C31")
Set r48 = Sheets("Priority Table").Range("J31")
Set r49 = Sheets("Priority Table").Range("Q31")
Set r50 = Sheets("Priority Table").Range("C42")
Set r51 = Sheets("Priority Table").Range("B11")
Set r52 = Sheets("Priority Table").Range("I11")
Set r53 = Sheets("Priority Table").Range("P11")
Set r54 = Sheets("Priority Table").Range("B22")
Set r55 = Sheets("Priority Table").Range("I22")
Set r56 = Sheets("Priority Table").Range("P22")
Set r57 = Sheets("Priority Table").Range("B33")
Set r58 = Sheets("Priority Table").Range("I33")
Set r59 = Sheets("Priority Table").Range("P33")
Set r60 = Sheets("Priority Table").Range("B44")
Set r61 = Sheets("Priority Table").Range("D6")
Set r62 = Sheets("Priority Table").Range("K6")
Set r63 = Sheets("Priority Table").Range("R6")
Set r64 = Sheets("Priority Table").Range("D17")
Set r65 = Sheets("Priority Table").Range("K17")
Set r66 = Sheets("Priority Table").Range("R17")
Set r67 = Sheets("Priority Table").Range("D28")
Set r68 = Sheets("Priority Table").Range("K28")
Set r69 = Sheets("Priority Table").Range("R28")
Set r70 = Sheets("Priority Table").Range("D39")
Set r71 = Sheets("Priority Table").Range("D7")
Set r72 = Sheets("Priority Table").Range("K7")
Set r73 = Sheets("Priority Table").Range("R7")
Set r74 = Sheets("Priority Table").Range("D18")
Set r75 = Sheets("Priority Table").Range("K18")
Set r76 = Sheets("Priority Table").Range("R18")
Set r77 = Sheets("Priority Table").Range("D29")
Set r78 = Sheets("Priority Table").Range("K29")
Set r79 = Sheets("Priority Table").Range("R29")
Set r80 = Sheets("Priority Table").Range("D40")
Set r81 = Sheets("Priority Table").Range("D8")
Set r82 = Sheets("Priority Table").Range("K8")
Set r83 = Sheets("Priority Table").Range("R8")
Set r84 = Sheets("Priority Table").Range("D19")
Set r85 = Sheets("Priority Table").Range("K19")
Set r86 = Sheets("Priority Table").Range("R19")
Set r87 = Sheets("Priority Table").Range("D30")
Set r88 = Sheets("Priority Table").Range("K30")
Set r89 = Sheets("Priority Table").Range("R30")
Set r90 = Sheets("Priority Table").Range("D41")
Set r91 = Sheets("Priority Table").Range("D9")
Set r92 = Sheets("Priority Table").Range("K9")
Set r93 = Sheets("Priority Table").Range("R9")
Set r94 = Sheets("Priority Table").Range("D20")
Set r95 = Sheets("Priority Table").Range("K20")
Set r96 = Sheets("Priority Table").Range("R20")
Set r97 = Sheets("Priority Table").Range("D31")
Set r98 = Sheets("Priority Table").Range("K31")
Set r99 = Sheets("Priority Table").Range("R31")
Set r100 = Sheets("Priority Table").Range("D42")
Set r101 = Sheets("Priority Table").Range("D10")
Set r102 = Sheets("Priority Table").Range("K10")
Set r103 = Sheets("Priority Table").Range("R10")
Set r104 = Sheets("Priority Table").Range("D21")
Set r105 = Sheets("Priority Table").Range("K21")
Set r106 = Sheets("Priority Table").Range("R21")
Set r107 = Sheets("Priority Table").Range("D32")
Set r108 = Sheets("Priority Table").Range("K32")
Set r109 = Sheets("Priority Table").Range("R32")
Set r110 = Sheets("Priority Table").Range("D43")
Set r111 = Sheets("Priority Table").Range("D11")
Set r112 = Sheets("Priority Table").Range("K11")
Set r113 = Sheets("Priority Table").Range("R11")
Set r114 = Sheets("Priority Table").Range("D22")
Set r115 = Sheets("Priority Table").Range("K22")
Set r116 = Sheets("Priority Table").Range("R22")
Set r117 = Sheets("Priority Table").Range("D33")
Set r118 = Sheets("Priority Table").Range("K33")
Set r119 = Sheets("Priority Table").Range("R33")
Set r120 = Sheets("Priority Table").Range("D44")
Set r121 = Sheets("Priority Table").Range("E4")
Set r122 = Sheets("Priority Table").Range("L4")
Set r123 = Sheets("Priority Table").Range("S4")
Set r124 = Sheets("Priority Table").Range("E15")
Set r125 = Sheets("Priority Table").Range("L15")
Set r126 = Sheets("Priority Table").Range("S15")
Set r127 = Sheets("Priority Table").Range("E26")
Set r128 = Sheets("Priority Table").Range("L26")
Set r129 = Sheets("Priority Table").Range("S26")
Set r130 = Sheets("Priority Table").Range("E37")
Set r131 = Sheets("Priority Table").Range("F4")
Set r132 = Sheets("Priority Table").Range("M4")
Set r133 = Sheets("Priority Table").Range("T4")
Set r134 = Sheets("Priority Table").Range("F15")
Set r135 = Sheets("Priority Table").Range("M15")
Set r136 = Sheets("Priority Table").Range("S15")
Set r137 = Sheets("Priority Table").Range("F26")
Set r138 = Sheets("Priority Table").Range("M26")
Set r139 = Sheets("Priority Table").Range("T26")
Set r140 = Sheets("Priority Table").Range("F37")
Set r141 = Sheets("Priority Table").Range("E6")
Set r142 = Sheets("Priority Table").Range("L6")
Set r143 = Sheets("Priority Table").Range("S6")
Set r144 = Sheets("Priority Table").Range("E17")
Set r145 = Sheets("Priority Table").Range("L17")
Set r146 = Sheets("Priority Table").Range("S17")
Set r147 = Sheets("Priority Table").Range("E28")
Set r148 = Sheets("Priority Table").Range("L28")
Set r149 = Sheets("Priority Table").Range("S28")
Set r150 = Sheets("Priority Table").Range("E39")
Set r151 = Sheets("Priority Table").Range("F6")
Set r152 = Sheets("Priority Table").Range("M6")
Set r153 = Sheets("Priority Table").Range("T6")
Set r154 = Sheets("Priority Table").Range("F17")
Set r155 = Sheets("Priority Table").Range("M17")
Set r156 = Sheets("Priority Table").Range("T17")
Set r157 = Sheets("Priority Table").Range("F28")
Set r158 = Sheets("Priority Table").Range("M28")
Set r159 = Sheets("Priority Table").Range("T28")
Set r160 = Sheets("Priority Table").Range("F39")
Set r161 = Sheets("Priority Table").Range("E8")
Set r162 = Sheets("Priority Table").Range("L8")
Set r163 = Sheets("Priority Table").Range("S8")
Set r164 = Sheets("Priority Table").Range("E19")
Set r165 = Sheets("Priority Table").Range("L19")
Set r166 = Sheets("Priority Table").Range("S19")
Set r167 = Sheets("Priority Table").Range("E30")
Set r168 = Sheets("Priority Table").Range("L30")
Set r169 = Sheets("Priority Table").Range("S30")
Set r170 = Sheets("Priority Table").Range("E41")
Set r171 = Sheets("Priority Table").Range("F8")
Set r172 = Sheets("Priority Table").Range("M8")
Set r173 = Sheets("Priority Table").Range("T8")
Set r174 = Sheets("Priority Table").Range("F19")
Set r175 = Sheets("Priority Table").Range("M19")
Set r176 = Sheets("Priority Table").Range("T19")
Set r177 = Sheets("Priority Table").Range("F30")
Set r178 = Sheets("Priority Table").Range("M30")
Set r179 = Sheets("Priority Table").Range("T30")
Set r180 = Sheets("Priority Table").Range("F41")
Set r181 = Sheets("Priority Table").Range("E10")
Set r182 = Sheets("Priority Table").Range("L10")
Set r183 = Sheets("Priority Table").Range("S10")
Set r184 = Sheets("Priority Table").Range("E21")
Set r185 = Sheets("Priority Table").Range("L21")
Set r186 = Sheets("Priority Table").Range("S21")
Set r187 = Sheets("Priority Table").Range("E32")
Set r188 = Sheets("Priority Table").Range("L32")
Set r189 = Sheets("Priority Table").Range("S32")
Set r190 = Sheets("Priority Table").Range("E43")
Set r191 = Sheets("Priority Table").Range("F10")
Set r192 = Sheets("Priority Table").Range("M10")
Set r193 = Sheets("Priority Table").Range("T10")
Set r194 = Sheets("Priority Table").Range("F21")
Set r195 = Sheets("Priority Table").Range("M21")
Set r196 = Sheets("Priority Table").Range("T21")
Set r197 = Sheets("Priority Table").Range("F32")
Set r198 = Sheets("Priority Table").Range("M32")
Set r199 = Sheets("Priority Table").Range("T32")
Set r200 = Sheets("Priority Table").Range("F43")
Set r201 = Sheets("Priority Table").Range("F11")
Set r202 = Sheets("Priority Table").Range("M11")
Set r203 = Sheets("Priority Table").Range("T11")
Set r204 = Sheets("Priority Table").Range("F22")
Set r205 = Sheets("Priority Table").Range("M22")
Set r206 = Sheets("Priority Table").Range("T22")
Set r207 = Sheets("Priority Table").Range("F33")
Set r208 = Sheets("Priority Table").Range("M33")
Set r209 = Sheets("Priority Table").Range("T33")
Set r210 = Sheets("Priority Table").Range("F44")
Set r211 = Sheets("Priority Table").Range("G6")
Set r212 = Sheets("Priority Table").Range("N6")
Set r213 = Sheets("Priority Table").Range("U6")
Set r214 = Sheets("Priority Table").Range("G17")
Set r215 = Sheets("Priority Table").Range("N17")
Set r216 = Sheets("Priority Table").Range("U17")
Set r217 = Sheets("Priority Table").Range("G28")
Set r218 = Sheets("Priority Table").Range("N28")
Set r219 = Sheets("Priority Table").Range("U28")
Set r220 = Sheets("Priority Table").Range("G39")
Set r221 = Sheets("Issue List").Range("B2")
Set r222 = Sheets("Issue List").Range("B3")
Set r223 = Sheets("Issue List").Range("B4")
Set r224 = Sheets("Issue List").Range("B5")
Set r225 = Sheets("Issue List").Range("B6")
Set r226 = Sheets("Issue List").Range("B7")
Set r227 = Sheets("Issue List").Range("B8")
Set r228 = Sheets("Issue List").Range("B9")
Set r229 = Sheets("Issue List").Range("B10")
Set r230 = Sheets("Issue List").Range("B11")
Set r231 = Sheets("Issue List").Range("C2")
Set r232 = Sheets("Issue List").Range("C3")
Set r233 = Sheets("Issue List").Range("C4")
Set r234 = Sheets("Issue List").Range("C5")
Set r235 = Sheets("Issue List").Range("C6")
Set r236 = Sheets("Issue List").Range("C7")
Set r237 = Sheets("Issue List").Range("C8")
Set r238 = Sheets("Issue List").Range("C9")
Set r239 = Sheets("Issue List").Range("C10")
Set r240 = Sheets("Issue List").Range("C11")
Set r241 = Sheets("Issue List").Range("D2")
Set r242 = Sheets("Issue List").Range("D3")
Set r243 = Sheets("Issue List").Range("D4")
Set r244 = Sheets("Issue List").Range("D5")
Set r245 = Sheets("Issue List").Range("D6")
Set r246 = Sheets("Issue List").Range("D7")
Set r247 = Sheets("Issue List").Range("D8")
Set r248 = Sheets("Issue List").Range("D9")
Set r249 = Sheets("Issue List").Range("D10")
Set r250 = Sheets("Issue List").Range("D11")
Set r251 = Sheets("Issue List").Range("E2")
Set r252 = Sheets("Issue List").Range("E3")
Set r253 = Sheets("Issue List").Range("E4")
Set r254 = Sheets("Issue List").Range("E5")
Set r255 = Sheets("Issue List").Range("E6")
Set r256 = Sheets("Issue List").Range("E7")
Set r257 = Sheets("Issue List").Range("E8")
Set r258 = Sheets("Issue List").Range("E9")
Set r259 = Sheets("Issue List").Range("E10")
Set r260 = Sheets("Issue List").Range("E11")
Set r261 = Sheets("Issue List").Range("F2")
Set r262 = Sheets("Issue List").Range("F3")
Set r263 = Sheets("Issue List").Range("F4")
Set r264 = Sheets("Issue List").Range("F5")
Set r265 = Sheets("Issue List").Range("F6")
Set r266 = Sheets("Issue List").Range("F7")
Set r267 = Sheets("Issue List").Range("F8")
Set r268 = Sheets("Issue List").Range("F9")
Set r269 = Sheets("Issue List").Range("F10")
Set r270 = Sheets("Issue List").Range("F11")
Set r271 = Sheets("Issue List").Range("G2")
Set r272 = Sheets("Issue List").Range("G3")
Set r273 = Sheets("Issue List").Range("G4")
Set r274 = Sheets("Issue List").Range("G5")
Set r275 = Sheets("Issue List").Range("G6")
Set r276 = Sheets("Issue List").Range("G7")
Set r277 = Sheets("Issue List").Range("G8")
Set r278 = Sheets("Issue List").Range("G9")
Set r279 = Sheets("Issue List").Range("G10")
Set r280 = Sheets("Issue List").Range("G11")
Set r281 = Sheets("Issue List").Range("H2")
Set r282 = Sheets("Issue List").Range("H3")
Set r283 = Sheets("Issue List").Range("H4")
Set r284 = Sheets("Issue List").Range("H5")
Set r285 = Sheets("Issue List").Range("H6")
Set r286 = Sheets("Issue List").Range("H7")
Set r287 = Sheets("Issue List").Range("H8")
Set r288 = Sheets("Issue List").Range("H9")
Set r289 = Sheets("Issue List").Range("H10")
Set r290 = Sheets("Issue List").Range("H11")
Set r291 = Sheets("Issue List").Range("I2")
Set r292 = Sheets("Issue List").Range("I3")
Set r293 = Sheets("Issue List").Range("I4")
Set r294 = Sheets("Issue List").Range("I5")
Set r295 = Sheets("Issue List").Range("I6")
Set r296 = Sheets("Issue List").Range("I7")
Set r297 = Sheets("Issue List").Range("I8")
Set r298 = Sheets("Issue List").Range("I9")
Set r299 = Sheets("Issue List").Range("I10")
Set r300 = Sheets("Issue List").Range("I11")
Set r301 = Sheets("Issue List").Range("J2")
Set r302 = Sheets("Issue List").Range("J3")
Set r303 = Sheets("Issue List").Range("J4")
Set r304 = Sheets("Issue List").Range("J5")
Set r305 = Sheets("Issue List").Range("J6")
Set r306 = Sheets("Issue List").Range("J7")
Set r307 = Sheets("Issue List").Range("J8")
Set r308 = Sheets("Issue List").Range("J9")
Set r309 = Sheets("Issue List").Range("J10")
Set r310 = Sheets("Issue List").Range("J11")
Set r311 = Sheets("Issue List").Range("K2")
Set r312 = Sheets("Issue List").Range("K3")
Set r313 = Sheets("Issue List").Range("K4")
Set r314 = Sheets("Issue List").Range("K5")
Set r315 = Sheets("Issue List").Range("K6")
Set r316 = Sheets("Issue List").Range("K7")
Set r317 = Sheets("Issue List").Range("K8")
Set r318 = Sheets("Issue List").Range("K9")
Set r319 = Sheets("Issue List").Range("K10")
Set r320 = Sheets("Issue List").Range("K11")
Set r321 = Sheets("Issue List").Range("L2")
Set r322 = Sheets("Issue List").Range("L3")
Set r323 = Sheets("Issue List").Range("L4")
Set r324 = Sheets("Issue List").Range("L5")
Set r325 = Sheets("Issue List").Range("L6")
Set r326 = Sheets("Issue List").Range("L7")
Set r327 = Sheets("Issue List").Range("L8")
Set r328 = Sheets("Issue List").Range("L9")
Set r329 = Sheets("Issue List").Range("L10")
Set r330 = Sheets("Issue List").Range("L11")
Set r331 = Sheets("Issue List").Range("M2")
Set r332 = Sheets("Issue List").Range("M3")
Set r333 = Sheets("Issue List").Range("M4")
Set r334 = Sheets("Issue List").Range("M5")
Set r335 = Sheets("Issue List").Range("M6")
Set r336 = Sheets("Issue List").Range("M7")
Set r337 = Sheets("Issue List").Range("M8")
Set r338 = Sheets("Issue List").Range("M9")
Set r339 = Sheets("Issue List").Range("M10")
Set r340 = Sheets("Issue List").Range("M11")
Set r341 = Sheets("Issue List").Range("P2")
Set r342 = Sheets("Issue List").Range("P3")
Set r343 = Sheets("Issue List").Range("P4")
Set r344 = Sheets("Issue List").Range("P5")
Set r345 = Sheets("Issue List").Range("P6")
Set r346 = Sheets("Issue List").Range("P7")
Set r347 = Sheets("Issue List").Range("P8")
Set r348 = Sheets("Issue List").Range("P9")
Set r349 = Sheets("Issue List").Range("P10")
Set r350 = Sheets("Issue List").Range("P11")
Set r351 = Sheets("Issue List").Range("Q2")
Set r352 = Sheets("Issue List").Range("Q3")
Set r353 = Sheets("Issue List").Range("Q4")
Set r354 = Sheets("Issue List").Range("Q5")
Set r355 = Sheets("Issue List").Range("Q6")
Set r356 = Sheets("Issue List").Range("Q7")
Set r357 = Sheets("Issue List").Range("Q8")
Set r358 = Sheets("Issue List").Range("Q9")
Set r359 = Sheets("Issue List").Range("Q10")
Set r360 = Sheets("Issue List").Range("Q11")
Set r361 = Sheets("Issue List").Range("R2")
Set r362 = Sheets("Issue List").Range("R3")
Set r363 = Sheets("Issue List").Range("R4")
Set r364 = Sheets("Issue List").Range("R5")
Set r365 = Sheets("Issue List").Range("R6")
Set r366 = Sheets("Issue List").Range("R7")
Set r367 = Sheets("Issue List").Range("R8")
Set r368 = Sheets("Issue List").Range("R9")
Set r369 = Sheets("Issue List").Range("R10")
Set r370 = Sheets("Issue List").Range("R11")
Set r371 = Sheets("Issue List").Range("S2")
Set r372 = Sheets("Issue List").Range("S3")
Set r373 = Sheets("Issue List").Range("S4")
Set r374 = Sheets("Issue List").Range("S5")
Set r375 = Sheets("Issue List").Range("S6")
Set r376 = Sheets("Issue List").Range("S7")
Set r377 = Sheets("Issue List").Range("S8")
Set r378 = Sheets("Issue List").Range("S9")
Set r379 = Sheets("Issue List").Range("S10")
Set r380 = Sheets("Issue List").Range("S11")
Set r381 = Sheets("Issue List").Range("T2")
Set r382 = Sheets("Issue List").Range("T3")
Set r383 = Sheets("Issue List").Range("T4")
Set r384 = Sheets("Issue List").Range("T5")
Set r385 = Sheets("Issue List").Range("T6")
Set r386 = Sheets("Issue List").Range("T7")
Set r387 = Sheets("Issue List").Range("T8")
Set r388 = Sheets("Issue List").Range("T9")
Set r389 = Sheets("Issue List").Range("T10")
Set r390 = Sheets("Issue List").Range("T11")
Set r391 = Sheets("Issue List").Range("U2")
Set r392 = Sheets("Issue List").Range("U3")
Set r393 = Sheets("Issue List").Range("U4")
Set r394 = Sheets("Issue List").Range("U5")
Set r395 = Sheets("Issue List").Range("U6")
Set r396 = Sheets("Issue List").Range("U7")
Set r397 = Sheets("Issue List").Range("U8")
Set r398 = Sheets("Issue List").Range("U9")
Set r399 = Sheets("Issue List").Range("U10")
Set r400 = Sheets("Issue List").Range("U11")
Set r401 = Sheets("Issue List").Range("V2")
Set r402 = Sheets("Issue List").Range("V3")
Set r403 = Sheets("Issue List").Range("V4")
Set r404 = Sheets("Issue List").Range("V5")
Set r405 = Sheets("Issue List").Range("V6")
Set r406 = Sheets("Issue List").Range("V7")
Set r407 = Sheets("Issue List").Range("V8")
Set r408 = Sheets("Issue List").Range("V9")
Set r409 = Sheets("Issue List").Range("V10")
Set r410 = Sheets("Issue List").Range("V11")
Set r411 = Sheets("Issue List").Range("W2")
Set r412 = Sheets("Issue List").Range("W3")
Set r413 = Sheets("Issue List").Range("W4")
Set r414 = Sheets("Issue List").Range("W5")
Set r415 = Sheets("Issue List").Range("W6")
Set r416 = Sheets("Issue List").Range("W7")
Set r417 = Sheets("Issue List").Range("W8")
Set r418 = Sheets("Issue List").Range("W9")
Set r419 = Sheets("Issue List").Range("W10")
Set r420 = Sheets("Issue List").Range("W11")
Set r421 = Sheets("Issue List").Range("X2")
Set r422 = Sheets("Issue List").Range("X3")
Set r423 = Sheets("Issue List").Range("X4")
Set r424 = Sheets("Issue List").Range("X5")
Set r425 = Sheets("Issue List").Range("X6")
Set r426 = Sheets("Issue List").Range("X7")
Set r427 = Sheets("Issue List").Range("X8")
Set r428 = Sheets("Issue List").Range("X9")
Set r429 = Sheets("Issue List").Range("X10")
Set r430 = Sheets("Issue List").Range("X11")
Set r431 = Sheets("Issue List").Range("Y2")
Set r432 = Sheets("Issue List").Range("Y3")
Set r433 = Sheets("Issue List").Range("Y4")
Set r434 = Sheets("Issue List").Range("Y5")
Set r435 = Sheets("Issue List").Range("Y6")
Set r436 = Sheets("Issue List").Range("Y7")
Set r437 = Sheets("Issue List").Range("Y8")
Set r438 = Sheets("Issue List").Range("Y9")
Set r439 = Sheets("Issue List").Range("Y10")
Set r440 = Sheets("Issue List").Range("Y11")
Set r441 = Sheets("Priority Table").Range("G4")
Set r442 = Sheets("Priority Table").Range("N4")
Set r443 = Sheets("Priority Table").Range("U4")
Set r444 = Sheets("Priority Table").Range("G15")
Set r445 = Sheets("Priority Table").Range("N15")
Set r446 = Sheets("Priority Table").Range("U15")
Set r447 = Sheets("Priority Table").Range("G26")
Set r448 = Sheets("Priority Table").Range("N26")
Set r449 = Sheets("Priority Table").Range("U26")
Set r450 = Sheets("Priority Table").Range("G37")
Set r451 = Sheets("Issue List").Range("O2")
Set r452 = Sheets("Issue List").Range("O3")
Set r453 = Sheets("Issue List").Range("O4")
Set r454 = Sheets("Issue List").Range("O5")
Set r455 = Sheets("Issue List").Range("O6")
Set r456 = Sheets("Issue List").Range("O7")
Set r457 = Sheets("Issue List").Range("O8")
Set r458 = Sheets("Issue List").Range("O9")
Set r459 = Sheets("Issue List").Range("O10")
Set r460 = Sheets("Issue List").Range("O11")
Set r461 = Sheets("Priority Table").Range("B2:G11")
Set r462 = Sheets("Priority Table").Range("I2:N11")
Set r463 = Sheets("Priority Table").Range("P2:U11")
Set r464 = Sheets("Priority Table").Range("B13:G22")
Set r465 = Sheets("Priority Table").Range("I13:N22")
Set r466 = Sheets("Priority Table").Range("P13:U22")
Set r467 = Sheets("Priority Table").Range("B24:G33")
Set r468 = Sheets("Priority Table").Range("I24:N33")
Set r469 = Sheets("Priority Table").Range("P24:U33")
Set r470 = Sheets("Priority Table").Range("B35:G44")


If Not (Intersect(Target, r461) Is Nothing) Then

Application.EnableEvents = False

    r221.Value = r1.Value
    r231.Value = r11.Value
    r251.Value = r31.Value
    r261.Value = r41.Value
    r271.Value = r51.Value
    r281.Value = r61.Value
    r291.Value = r71.Value
    r301.Value = r81.Value
    r311.Value = r91.Value
    r321.Value = r101.Value
    r331.Value = r111.Value
    r451.Value = r441.Value
    r361.Value = r141.Value
    r371.Value = r151.Value
    r381.Value = r161.Value
    r391.Value = r171.Value
    r401.Value = r181.Value
    r411.Value = r191.Value
    r421.Value = r201.Value
    r431.Value = r211.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r462) Is Nothing) Then

Application.EnableEvents = False

    r222.Value = r2.Value
    r232.Value = r12.Value
    r252.Value = r32.Value
    r262.Value = r42.Value
    r272.Value = r52.Value
    r282.Value = r62.Value
    r292.Value = r72.Value
    r302.Value = r82.Value
    r312.Value = r92.Value
    r322.Value = r102.Value
    r332.Value = r112.Value
    r452.Value = r442.Value
    r362.Value = r142.Value
    r372.Value = r152.Value
    r382.Value = r162.Value
    r392.Value = r172.Value
    r402.Value = r182.Value
    r412.Value = r192.Value
    r422.Value = r202.Value
    r432.Value = r212.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r463) Is Nothing) Then

Application.EnableEvents = False

    r223.Value = r3.Value
    r233.Value = r13.Value
    r253.Value = r33.Value
    r263.Value = r43.Value
    r273.Value = r53.Value
    r283.Value = r63.Value
    r293.Value = r73.Value
    r303.Value = r83.Value
    r313.Value = r93.Value
    r323.Value = r103.Value
    r333.Value = r113.Value
    r453.Value = r443.Value
    r363.Value = r143.Value
    r373.Value = r153.Value
    r383.Value = r163.Value
    r393.Value = r173.Value
    r403.Value = r183.Value
    r413.Value = r193.Value
    r423.Value = r203.Value
    r433.Value = r213.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r464) Is Nothing) Then

Application.EnableEvents = False

    r224.Value = r4.Value
    r234.Value = r14.Value
    r254.Value = r34.Value
    r264.Value = r44.Value
    r274.Value = r54.Value
    r284.Value = r64.Value
    r294.Value = r74.Value
    r304.Value = r84.Value
    r314.Value = r94.Value
    r324.Value = r104.Value
    r334.Value = r114.Value
    r454.Value = r444.Value
    r364.Value = r144.Value
    r374.Value = r154.Value
    r384.Value = r164.Value
    r394.Value = r174.Value
    r404.Value = r184.Value
    r414.Value = r194.Value
    r424.Value = r204.Value
    r434.Value = r214.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r465) Is Nothing) Then

Application.EnableEvents = False

    r225.Value = r5.Value
    r235.Value = r15.Value
    r255.Value = r35.Value
    r265.Value = r45.Value
    r275.Value = r55.Value
    r285.Value = r65.Value
    r295.Value = r75.Value
    r305.Value = r85.Value
    r315.Value = r95.Value
    r325.Value = r105.Value
    r335.Value = r115.Value
    r455.Value = r445.Value
    r365.Value = r145.Value
    r375.Value = r155.Value
    r385.Value = r165.Value
    r395.Value = r175.Value
    r405.Value = r185.Value
    r415.Value = r195.Value
    r425.Value = r205.Value
    r435.Value = r215.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r466) Is Nothing) Then

Application.EnableEvents = False

    r226.Value = r6.Value
    r236.Value = r16.Value
    r256.Value = r36.Value
    r266.Value = r46.Value
    r276.Value = r56.Value
    r286.Value = r66.Value
    r296.Value = r76.Value
    r306.Value = r86.Value
    r316.Value = r96.Value
    r326.Value = r106.Value
    r336.Value = r116.Value
    r456.Value = r446.Value
    r366.Value = r146.Value
    r376.Value = r156.Value
    r386.Value = r166.Value
    r396.Value = r176.Value
    r406.Value = r186.Value
    r416.Value = r196.Value
    r426.Value = r206.Value
    r436.Value = r216.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r467) Is Nothing) Then

Application.EnableEvents = False

    r227.Value = r7.Value
    r237.Value = r17.Value
    r257.Value = r37.Value
    r267.Value = r47.Value
    r277.Value = r57.Value
    r287.Value = r67.Value
    r297.Value = r77.Value
    r307.Value = r87.Value
    r317.Value = r97.Value
    r327.Value = r107.Value
    r337.Value = r117.Value
    r457.Value = r447.Value
    r367.Value = r147.Value
    r377.Value = r157.Value
    r387.Value = r167.Value
    r397.Value = r177.Value
    r407.Value = r187.Value
    r417.Value = r197.Value
    r427.Value = r207.Value
    r437.Value = r217.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r468) Is Nothing) Then

Application.EnableEvents = False

    r228.Value = r8.Value
    r238.Value = r18.Value
    r258.Value = r38.Value
    r268.Value = r48.Value
    r278.Value = r58.Value
    r288.Value = r68.Value
    r298.Value = r78.Value
    r308.Value = r88.Value
    r318.Value = r98.Value
    r328.Value = r108.Value
    r338.Value = r118.Value
    r458.Value = r448.Value
    r368.Value = r148.Value
    r378.Value = r158.Value
    r388.Value = r168.Value
    r398.Value = r178.Value
    r408.Value = r188.Value
    r418.Value = r198.Value
    r428.Value = r208.Value
    r438.Value = r218.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r469) Is Nothing) Then

Application.EnableEvents = False

    r229.Value = r9.Value
    r239.Value = r19.Value
    r259.Value = r39.Value
    r269.Value = r49.Value
    r279.Value = r59.Value
    r289.Value = r69.Value
    r299.Value = r79.Value
    r309.Value = r89.Value
    r319.Value = r99.Value
    r329.Value = r109.Value
    r339.Value = r119.Value
    r459.Value = r449.Value
    r369.Value = r149.Value
    r379.Value = r159.Value
    r389.Value = r169.Value
    r399.Value = r179.Value
    r409.Value = r189.Value
    r419.Value = r199.Value
    r429.Value = r209.Value
    r439.Value = r219.Value

Application.EnableEvents = True

End If

If Not (Intersect(Target, r470) Is Nothing) Then

Application.EnableEvents = False

    r230.Value = r10.Value
    r240.Value = r20.Value
    r260.Value = r40.Value
    r270.Value = r50.Value
    r280.Value = r60.Value
    r290.Value = r70.Value
    r300.Value = r80.Value
    r310.Value = r90.Value
    r320.Value = r100.Value
    r330.Value = r110.Value
    r340.Value = r120.Value
    r460.Value = r450.Value
    r370.Value = r150.Value
    r380.Value = r160.Value
    r390.Value = r170.Value
    r400.Value = r180.Value
    r410.Value = r190.Value
    r420.Value = r200.Value
    r430.Value = r210.Value
    r440.Value = r220.Value

Application.EnableEvents = True

End If

End Sub

My best guess of how to tighten up the code would be to move the 470 lines of set variable as range into a separate procedure that could be accessed from all of the different procedures in the sheet (those same 470 lines of code are repeated in the use of both the New Issue button and the Prioritize Issues button). I have been looking for several weeks for a way to do that but have failed to find any way of writing the code that allows me to do that.

I am not in any way opposed to changing the way the variables are named, I've just always found sequential lists to be the easiest way to manage large sets of ranges.

Improve this question
\$\endgroup\$
3
  • \$\begingroup\$ Wouldn't it be more efficient for your company to purchase helpdesk software? \$\endgroup\$
    – jkpieterse
    Feb 22, 2017 at 12:21
  • 2
    \$\begingroup\$ Thanks for adding that. I think we'll be able to help you out a lot here. \$\endgroup\$
    – Kaz
    Feb 27, 2017 at 15:56
  • 5
    \$\begingroup\$ Wow, that's awful. Welcome to Code Review, you definitely came to the right place :-) \$\endgroup\$
    – Mast ♦
    Feb 27, 2017 at 16:02

1 Answer 1

Reset to default
4
\$\begingroup\$

You have 12 identically-formatted tables; I would expect readable/maintainable code to read like it has some kind of concept for one of these tables. A class module would have been perfect for this. Instead we're looking at a completely cryptic sequential list of no less than 470 [undeclared] object variables, 60 of which are assigned but never used anywhere in the procedure's scope.

The procedure in question is an event handler that gets invoked everytime anything changes on that worksheet: its content should be minimal and performant, to avoid sluggishness.

The handler procedure is dereferencing the "Priority Table" and "Issue List" worksheets countless times... which is useless overhead. You could have dereferenced it once, with a With block:

With ThisWorkbook.Worksheets("Priority Table")
    Set r1 = .Range("B2")
    '...
End With

With ThisWorkbook.Worksheets("Issue List")
    Set r221 = .Range("B2")
    '...
End With

But that's not addressing the elephant in the room at all: under no circumstance is it ever a good idea to have 470 variables anywhere. Not in that procedure, not in another either. The approach is simply wrong.

Not to mention, none of these variables are actually declared: adding Option Explicit at the top of your module, and then getting your code to compile, is going to be quite a pain in the neck.

I said above:

You have 12 identically-formatted tables; I would expect readable/maintainable code to read like it has some kind of concept for one of these tables.

The key here is abstraction - that's the difference between a macro-recorder script, and a well-written program.

I would start by naming things. Take one of these 12 tables, and describe it:

  • There's a Heading, or Title in a merged cell at the top.
  • There's a code for the IssueSummary.
  • There's a DateReported date.
  • There's a Boolean value to indicate whether it HasNextFollowUp
  • There's another Boolean value to indicate whether it IsDue
  • There's an AdjustFollowUp code. I'd make the last two tables (red headings) adhere to the format of the other tables, and move the Notes heading down to match the position of the Notes heading in every other table.
  • There's a list of up to 6 ReportBackTo codes.
  • There's a PriorityCode
  • There's a TicketFieldCode
  • There's a TicketNumber
  • There's a FirstFollowUp code
  • There's a SecondFollowUp code
  • There's a ThirdFollowUp code
  • There's a Notes field
  • There's an OnTime code that's not quite clear, that possibly refers to each follow-up codes, and the Resolved code.

I'd make a class module that exposes all the above as read-write properties. Then I'd add some meta-properties so that the table object can know how to "draw" itself:

  • HeadingRow and HeadingColumn, to describe the position of the left-most cell in the table's header.

Then I'd expose some Update method/procedure, that takes a worksheet parameter and dumps its values into that sheet - the procedure would know how to offset each value from the HeadingRow and HeadingColumn position.

I'd have some code to create and configure the 12 instances of that class in global scope, so that the handler can access them. And because naming variables with a numeric suffix is a code smell that basically says "you need a data structure", I'd actually be declaring 1 variable - an array that contains the 12 objects:

Option Explicit
Public PriorityTables(1 To 12) As PriorityTable

Public Sub InitializeTable(ByVal index As Long, ByVal headerCell As Range)
    Set PriorityTables(index) = New PriorityTable
    With PriorityTables(index)
        .HeadingRow = headerCell.Row
        .HeadingColumn = headerCell.Column
        .Update Sheet1
    End With
End Sub

The Workbook_Open handler could conceivably call that InitializeTable procedure in a loop, so you have your 12 tables in the global array as soon as the workbook is open.

There's another elephant in the room though: all the macro does, is assign a given cell the value of another cell. 410 times.

It looks like the entire macro could be replaced with worksheet formulas doing =TheOtherSheet!ZZ42, where ZZ42 is whatever cell is supposed to be referred to.

For example, r440.Value is r220.Value, so the formula in 'Issue List'!Y11 could simply be ='Priority Table'!G39, and so on, for everything; the worksheet would keep itself up-to-date without involving any VBA code.

Improve this answer
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.