skeeeter56
New Member
- Joined
- Nov 26, 2016
- Messages
- 42
- Office Version
- 2019
- Platform
- Windows
A kind member kindly assisted me and provided this code to me and it works perfectly. It runs when you click a button.
I want it to copy 2 more values from the same sheet and also copy them to the destination sheet's. I have tried various things with no luck.
This code looks to see if a value is true and then copies a range to a location on 1 of 5 sheets.
What I need is for for 2 more values to be copied to the new sheet
I have tried this but get an error
I have tried various ways but get an error
Below is 1 of the 5 pages they are all the same format, only difference is the number of columns, this based on how many rows are in each of the groups. Each group has 14 Ranges 1 for each colounm C to P.
The image below is of 1 of the destination sheets. The working code copies into Row 6 values in the range if it finds True, what I want is also for it to copy the Vale in C8 to P8 and C9 to P9 and to place these into the correct group sheet at C2 and E2
I want it to copy 2 more values from the same sheet and also copy them to the destination sheet's. I have tried various things with no luck.
This code looks to see if a value is true and then copies a range to a location on 1 of 5 sheets.
What I need is for for 2 more values to be copied to the new sheet
Dim shData As Worksheet, shGroup As Worksheet
Dim arrSh As Variant, arrCe As Variant, arrRn As Variant, arrCl As Variant
Dim i As Long, j As Long, k As Long, lr As Long
Application.ScreenUpdating = False
arrSh = Array("Nunawading", "Vermont", "Mitcham", "Blackburn", "Box Hill 1", "Box Hill 2") 'These are the destination Sheet Names
arrCe = Array(21, 31, 41, 56, 75, 76) 'These are the rows that are either blank, True or False. If true then it copies to destinations sheet
arrRn = Array("Nuna", "Verm", "Mitch", "Black", "Boxh", "Boxhi") 'These are the ranges in each colunm for each group there are 14 per group
arrCl = Array("Clear1", "Clear2", "Clear3", "Clear4", "Clear5", "Clear6") 'After job is printed it clears the cells.
Set shData = ThisWorkbook.Worksheets("Week Commencing")
For i = 0 To UBound(arrSh)
Set shGroup = Sheets(arrSh(i))
k = 1
For j = Columns("C").Column To Columns("P").Column
If shData.Cells(arrCe(i), j) = True Then
shData.Range(arrRn(i) & k).Copy
'lr = 6
shGroup.Range("D6").PasteSpecial Paste:=xlPasteValues, Transpose:=True
shGroup.PrintPreview
End If
k = k + 1
Next j
Next i
For i = 0 To UBound(arrSh) '
Set shGroup = Sheets(arrSh(i))
shGroup.Range(arrCl(i)).ClearContents
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I have tried this but get an error
If shData.Cells(arrCe(i), j) = True Then
shData.Range(arrRn(i) & k).Copy
'lr = 6
shGroup.Range("D6").PasteSpecial Paste:=xlPasteValues, Transpose:=True
shData.Range(arrNm(i) & k).Copy
shGroup.Range("C2").PasteSpecial Paste:=xlPasteValues
shData.Range(arrCo(i) & k).Copy
shGroup.Range("E2").PasteSpecial Paste:=xlPasteValues
I have tried various ways but get an error
| testing UMS.xlsm | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 3 | ||||||||||||||||||
| 4 | ||||||||||||||||||
| 5 | ||||||||||||||||||
| 6 | 1 | 3 | 1 | 4 | 2 | 3 | 1 | 2 | 2 | 2 | 2 | 2 | 4 | 2 | ||||
| 7 | 2 | |||||||||||||||||
| 8 | Nunawading | Total Pri. | Test 1 | Test 2 | Test3 | Test 4 | Test 5 | Test 6 | Test 7 | Test 8 | Test 9 | Test 10 | Test 11 | |||||
| 9 | 123 | 345 | 678 | 912 | 345 | 678 | 987 | 456 | 123 | 654 | 951 | |||||||
| 10 | 2 | 458 | 24 | 35 | 46 | 57 | 68 | 79 | 90 | 101 | 112 | 123 | 134 | |||||
| 11 | 3 | 846 | 25 | 42 | 59 | 76 | 93 | 110 | 127 | 144 | 161 | 178 | 195 | |||||
| 12 | 4 | 785 | 26 | 49 | 72 | 95 | 118 | 141 | 164 | 187 | 210 | 233 | 256 | |||||
| 13 | 5 | 850 | 27 | 56 | 85 | 114 | 143 | 172 | 201 | 230 | 259 | 288 | 317 | |||||
| 14 | 6 | 786 | 28 | 63 | 98 | 133 | 168 | 203 | 238 | 273 | 308 | 343 | 378 | |||||
| 15 | 7 | 823 | 29 | 70 | 111 | 152 | 193 | 234 | 275 | 316 | 357 | 398 | 439 | |||||
| 16 | 8 | 202 | 30 | 77 | 124 | 171 | 218 | 265 | 312 | 359 | 406 | 453 | 500 | |||||
| 17 | 9 | 808 | 31 | 84 | 137 | 190 | 243 | 296 | 349 | 402 | 455 | 508 | 561 | |||||
| 18 | 10 | 900 | 32 | 91 | 150 | 209 | 268 | 327 | 386 | 445 | 504 | 563 | 622 | |||||
| 19 | Boxes | 34 | 33 | 98 | 163 | 228 | 293 | 358 | 423 | 488 | 553 | 618 | 683 | |||||
| 20 | 285 | 665 | 1045 | 1425 | 1805 | 2185 | 2565 | 2945 | 3325 | 3705 | 4085 | |||||||
| 21 | SUMPRODUCT(ISTEXT(Verm1)+ISNUMBER(Nuna1))>0 | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | ||||||
| 22 | Vermont | Test 1 | Test 2 | Test3 | Test 4 | Test 5 | Test 6 | Test 7 | Test 8 | Test 9 | Test 10 | Test 11 | ||||||
| 23 | 21 | 950 | 34 | 531 | 602 | 222 | 95 | |||||||||||
| 24 | 22 | 850 | 35 | 574 | 651 | 239 | 103 | |||||||||||
| 25 | 23 | 900 | 36 | 617 | 700 | 256 | 111 | |||||||||||
| 26 | 25 | 750 | 37 | 660 | 749 | 273 | 119 | |||||||||||
| 27 | 26 | 850 | 38 | 703 | 798 | 290 | 127 | |||||||||||
| 28 | 28 | 850 | 39 | 746 | 847 | 307 | 135 | |||||||||||
| 29 | 29 | 850 | 40 | 789 | 896 | 324 | 143 | |||||||||||
| 30 | 259 | 4620 | 5243 | 1911 | 833 | |||||||||||||
| 31 | SUMPRODUCT(ISTEXT(Verm1)+ISNUMBER(Verm1))>0 | TRUE | FALSE | FALSE | TRUE | FALSE | ||||||||||||
| 32 | Mitcham | Test 1 | Test 2 | Test3 | Test 4 | Test 5 | Test 6 | Test 7 | Test 8 | Test 9 | Test 10 | Test 11 | ||||||
| 33 | 31 | 450 | 34 | 105 | 176 | 247 | ||||||||||||
| 34 | 32 | 800 | 35 | 112 | 189 | 266 | ||||||||||||
| 35 | 33 | 800 | 36 | 119 | 202 | 285 | ||||||||||||
| 36 | 34 | 800 | 37 | 126 | 215 | 304 | ||||||||||||
| 37 | 35 | 800 | 38 | 133 | 228 | 323 | ||||||||||||
| 38 | 36 | 750 | 39 | 140 | 241 | 342 | ||||||||||||
| 39 | 37 | 350 | 40 | 147 | 254 | 361 | ||||||||||||
| 40 | 259 | 882 | 1505 | 2128 | ||||||||||||||
| 41 | SUMPRODUCT(ISTEXT(Verm1)+ISNUMBER(Verm1))>0 | TRUE | TRUE | TRUE | TRUE | |||||||||||||
| 42 | Blackburn | Test 1 | Test 2 | Test3 | Test 4 | Test 5 | Test 6 | Test 7 | Test 8 | Test 9 | Test 10 | Test 11 | ||||||
| 43 | 41 | 450 | 24 | 35 | 46 | 57 | 68 | 79 | 90 | 101 | 112 | 123 | 134 | |||||
| 44 | 42 | 450 | 25 | 42 | 59 | 76 | 93 | 110 | 127 | 144 | 161 | 178 | 195 | |||||
| 45 | 43 | 700 | 26 | 49 | 72 | 95 | 118 | 141 | 164 | 187 | 210 | 233 | 256 | |||||
| 46 | 44 | 850 | 27 | 56 | 85 | 114 | 143 | 172 | 201 | 230 | 259 | 288 | 317 | |||||
| 47 | 45 | 950 | 28 | 63 | 98 | 133 | 168 | 203 | 238 | 273 | 308 | 343 | 378 | |||||
| 48 | 46 | 900 | 29 | 70 | 111 | 152 | 193 | 234 | 275 | 316 | 357 | 398 | 439 | |||||
| 49 | 47 | 750 | 30 | 77 | 124 | 171 | 218 | 265 | 312 | 359 | 406 | 453 | 500 | |||||
| 50 | 48 | 950 | 31 | 84 | 137 | 190 | 243 | 296 | 349 | 402 | 455 | 508 | 561 | |||||
| 51 | 49 | 900 | 32 | 91 | 150 | 209 | 268 | 327 | 386 | 445 | 504 | 563 | 622 | |||||
| 52 | 50 | 400 | 33 | 98 | 163 | 228 | 293 | 358 | 423 | 488 | 553 | 618 | 683 | |||||
| 53 | 51 | 950 | 34 | 105 | 176 | 247 | 318 | 389 | 460 | 531 | 602 | 673 | 744 | |||||
| 54 | 52 | 950 | 35 | 112 | 189 | 266 | 343 | 420 | 497 | 574 | 651 | 728 | 805 | |||||
| 55 | 354 | 882 | 1410 | 1938 | 2466 | 2994 | 3522 | 4050 | 4578 | 5106 | 5634 | |||||||
| 56 | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | |||||||
| 57 | Box Hill | Test 1 | Test 2 | Test3 | Test 4 | Test 5 | Test 6 | Test 7 | Test 8 | Test 9 | Test 10 | Test 11 | ||||||
| 59 | 61 | 600 | 30 | 124 | 359 | 406 | 453 | 500 | ||||||||||
| 60 | 62 | 750 | 31 | 137 | 402 | 455 | 508 | 561 | ||||||||||
| 61 | 63 | 800 | 32 | 150 | 445 | 504 | 563 | 622 | ||||||||||
| 62 | 64 | 850 | 33 | 163 | 488 | 553 | 618 | 683 | ||||||||||
| 63 | 65 | 700 | 34 | 176 | 531 | 602 | 673 | 744 | ||||||||||
| 64 | 66 | 650 | 35 | 189 | 574 | 651 | 728 | 805 | ||||||||||
| 65 | 67 | 200 | 36 | 202 | 617 | 700 | 783 | 866 | ||||||||||
| 66 | 68 | 1100 | 37 | 215 | 660 | 749 | 838 | 927 | ||||||||||
| 67 | 69 | 930 | 38 | 228 | 703 | 798 | 893 | 988 | ||||||||||
| 68 | 70 | 800 | 39 | 241 | 746 | 847 | 948 | 1049 | ||||||||||
| 69 | 71 | 860 | 40 | 254 | 789 | 896 | 1003 | 1110 | ||||||||||
| 70 | 72 | 950 | 41 | 267 | 832 | 945 | 1058 | 1171 | ||||||||||
| 71 | 73 | 750 | 42 | 280 | 875 | 994 | 1113 | 1232 | ||||||||||
| 72 | 74 | 400 | 43 | 293 | 918 | 1043 | 1168 | 1293 | ||||||||||
| 73 | 75 | 800 | 44 | 306 | 961 | 1092 | 1223 | 1354 | ||||||||||
| 74 | 76 | 300 | 45 | 319 | 1004 | 1141 | 1278 | 1415 | ||||||||||
| 75 | TRUE | TRUE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | ||||||
| 76 | TRUE | TRUE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | ||||||
| 77 | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | ||||||||||||
Week Commencing | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C20:P20 | C20 | =SUM(C10:C19) |
| C21 | C21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna1))<>ROWS(Nuna1)),C6<>2,0) |
| D21 | D21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna2))<>ROWS(Nuna2)),D6<>2,0) |
| E21 | E21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna3))<>ROWS(Nuna3)),E6<>2,0) |
| F21 | F21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna4))<>ROWS(Nuna4)),F6<>2,0) |
| G21 | G21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna5))<>ROWS(Nuna5)),G6<>2,0) |
| H21 | H21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna6))<>ROWS(Nuna6)),H6<>2,0) |
| I21 | I21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna7))<>ROWS(Nuna7)),I6<>2,0) |
| J21 | J21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna8))<>ROWS(Nuna8)),J6<>2,0) |
| K21 | K21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna9))<>ROWS(Nuna9)),K6<>2,0) |
| L21 | L21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna10))<>ROWS(Nuna10)),L6<>2,0) |
| M21 | M21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna11))<>ROWS(Nuna11)),M6<>2,0) |
| N21 | N21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna12))<>ROWS(Nuna12)),N6<>2,0) |
| O21 | O21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna13))<>ROWS(Nuna13)),O6<>2,0) |
| P21 | P21 | =IF(AND(SUMPRODUCT(--ISBLANK(Nuna14))<>ROWS(Nuna14)),P6<>2,0) |
| C22:P22 | C22 | =C8 |
| C30:P30,C40:P40 | C30 | =SUM(C23:C29) |
| C31 | C31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm1))<>ROWS(Verm1)),C6<>2,0) |
| D31 | D31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm2))<>ROWS(Verm2)),D6<>2,0) |
| E31 | E31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm3))<>ROWS(Verm3)),E6<>2,0) |
| F31 | F31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm4))<>ROWS(Verm4)),F6<>2,0) |
| G31 | G31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm5))<>ROWS(Verm5)),G6<>2,0) |
| H31 | H31 | =IF(AND(SUMPRODUCT(--ISBLANK(verm6))<>ROWS(verm6)),H6<>2,0) |
| I31 | I31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm7))<>ROWS(Verm7)),I6<>2,0) |
| J31 | J31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm8))<>ROWS(Verm8)),J6<>2,0) |
| K31 | K31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm9))<>ROWS(Verm9)),K6<>2,0) |
| L31 | L31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm10))<>ROWS(Verm10)),L6<>2,0) |
| M31 | M31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm12))<>ROWS(Verm12)),M6<>2,0) |
| N31 | N31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm13))<>ROWS(Verm13)),N6<>2,0) |
| O31 | O31 | =IF(AND(SUMPRODUCT(--ISBLANK(Verm14))<>ROWS(Verm14)),O6<>2,0) |
| P31 | P31 | =IF(AND(SUMPRODUCT(--ISBLANK(verm15))<>ROWS(verm15)),P6<>2,0) |
| C32:P32 | C32 | =C8 |
| C41 | C41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch1))<>ROWS(Mitch1)),C6<>2,0) |
| D41 | D41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch2))<>ROWS(Mitch2)),D6<>2,0) |
| E41 | E41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch3))<>ROWS(Mitch3)),E6<>2,0) |
| F41 | F41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch4))<>ROWS(Mitch4)),F6<>2,0) |
| G41 | G41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch5))<>ROWS(Mitch5)),G6<>2,0) |
| H41 | H41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch6))<>ROWS(Mitch6)),H6<>2,0) |
| I41 | I41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch7))<>ROWS(Mitch7)),I6<>2,0) |
| J41 | J41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch8))<>ROWS(Mitch8)),J6<>2,0) |
| K41 | K41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch9))<>ROWS(Mitch9)),K6<>2,0) |
| L41 | L41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch10))<>ROWS(Mitch10)),L6<>2,0) |
| M41 | M41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch11))<>ROWS(Mitch11)),M6<>2,0) |
| N41 | N41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch12))<>ROWS(Mitch12)),N6<>2,0) |
| O41 | O41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch13))<>ROWS(Mitch13)),O6<>2,0) |
| P41 | P41 | =IF(AND(SUMPRODUCT(--ISBLANK(Mitch14))<>ROWS(Mitch14)),P6<>2,0) |
| C42:P42 | C42 | =C8 |
| C55:P55 | D55 | =SUM(D43:D54) |
| D56 | D56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black2))<>ROWS(Black2)),D6<>2,0) |
| E56 | E56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black3))<>ROWS(Black3)),E6<>2,0) |
| F56 | F56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black4))<>ROWS(Black4)),F6<>2,0) |
| G56 | G56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black5))<>ROWS(Black5)),G6<>2,0) |
| H56 | H56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black6))<>ROWS(Black6)),H6<>2,0) |
| I56 | I56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black7))<>ROWS(Black7)),I6<>2,0) |
| J56 | J56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black8))<>ROWS(Black8)),J6<>2,0) |
| K56 | K56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black9))<>ROWS(Black9)),K6<>2,0) |
| L56 | L56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black10))<>ROWS(Black10)),L6<>2,0) |
| M56 | M56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black11))<>ROWS(Black11)),M6<>2,0) |
| N56 | N56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black12))<>ROWS(Black12)),N6<>2,0) |
| O56 | O56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black13))<>ROWS(Black13)),O6<>2,0) |
| P56 | P56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black14))<>ROWS(Black14)),P6<>2,0) |
| C57:P57 | D57 | =D8 |
| C56 | C56 | =IF(AND(SUMPRODUCT(--ISBLANK(Black1))<>ROWS(Black1)),C6<>2,0) |
| C75,G75:P75 | C75 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxh1))<>ROWS(Boxh1)),C6<>2,0) |
| D75 | D75 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxh2))<>ROWS(Boxh2)),D6<>2,0) |
| E75 | E75 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxh3))<>ROWS(Boxh3)),E6<>2,0) |
| F75 | F75 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxh4))<>ROWS(Boxh4)),F6<>2,0) |
| C76,G76:P76 | C76 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhi1))<>ROWS(Boxhi1)),C6<>2,0) |
| D76 | D76 | =IF(AND(SUMPRODUCT(--ISBLANK(D67:D74))<>ROWS(D67:D74)),D6<>2,0) |
| E76 | E76 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhi3))<>ROWS(Boxhi3)),E6<>2,0) |
| F76 | F76 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhi4))<>ROWS(Boxhi4)),F6<>2,0) |
| C77 | C77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill1))<>ROWS(Boxhill1)),C6<>2,0) |
| D77 | D77 | =IF(AND(SUMPRODUCT(--ISBLANK(BoxHill2))<>ROWS(BoxHill2)),D6<>2,0) |
| E77 | E77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill3))<>ROWS(Boxhill3)),E6<>2,0) |
| F77 | F77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill4))<>ROWS(Boxhill4)),F6<>2,0) |
| G77 | G77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill5))<>ROWS(Boxhill5)),G6<>2,0) |
| H77 | H77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill6))<>ROWS(Boxhill6)),H6<>2,0) |
| I77 | I77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill7))<>ROWS(Boxhill7)),I6<>2,0) |
| J77 | J77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill8))<>ROWS(Boxhill8)),J6<>2,0) |
| K77 | K77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill9))<>ROWS(Boxhill9)),K6<>2,0) |
| L77 | L77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill10))<>ROWS(Boxhill10)),L6<>2,0) |
| M77 | M77 | =IF(AND(SUMPRODUCT(--ISBLANK(M59:M74))<>ROWS(M59:M74)),M6<>2,0) |
| N77 | N77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill12))<>ROWS(Boxhill12)),N6<>2,0) |
| O77 | O77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill13))<>ROWS(Boxhill13)),O6<>2,0) |
| P77 | P77 | =IF(AND(SUMPRODUCT(--ISBLANK(Boxhill14))<>ROWS(Boxhill14)),P6<>2,0) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Black1 | ='Week Commencing'!$C$43:$C$54 | C55:C56 |
| Black10 | ='Week Commencing'!$L$43:$L$54 | L55:L56 |
| Black11 | ='Week Commencing'!$M$43:$M$54 | M55:M56 |
| Black12 | ='Week Commencing'!$N$43:$N$54 | N55:N56 |
| Black13 | ='Week Commencing'!$O$43:$O$54 | O55:O56 |
| Black14 | ='Week Commencing'!$P$43:$P$54 | P55:P56 |
| Black2 | ='Week Commencing'!$D$43:$D$54 | D55:D56 |
| Black3 | ='Week Commencing'!$E$43:$E$54 | E55:E56 |
| Black4 | ='Week Commencing'!$F$43:$F$54 | F55:F56 |
| Black5 | ='Week Commencing'!$G$43:$G$54 | G55:G56 |
| Black6 | ='Week Commencing'!$H$43:$H$54 | H55:H56 |
| Black7 | ='Week Commencing'!$I$43:$I$54 | I55:I56 |
| Black8 | ='Week Commencing'!$J$43:$J$54 | J55:J56 |
| Black9 | ='Week Commencing'!$K$43:$K$54 | K55:K56 |
| BNlack12 | ='Week Commencing'!$N$43:$N$54 | N55:N56 |
| Boxh1 | ='Week Commencing'!$C$59:$C$66 | G75:P75, C75, C77 |
| Boxh10 | ='Week Commencing'!$L$59:$L$66 | L77 |
| Boxh11 | ='Week Commencing'!$M$59:$M$66 | M77 |
| Boxh12 | ='Week Commencing'!$N$59:$N$66 | N77 |
| Boxh13 | ='Week Commencing'!$N$59:$N$66 | N77 |
| Boxh14 | ='Week Commencing'!$O$59:$O$66 | O77 |
| Boxh15 | ='Week Commencing'!$P$59:$P$66 | P77 |
| Boxh2 | ='Week Commencing'!$D$59:$D$66 | D75, D77 |
| Boxh3 | ='Week Commencing'!$E$59:$E$66 | E75, E77 |
| Boxh4 | ='Week Commencing'!$F$59:$F$66 | F75, F77 |
| Boxh5 | ='Week Commencing'!$G$59:$G$66 | G77 |
| Boxh6 | ='Week Commencing'!$H$59:$H$66 | H77 |
| Boxh7 | ='Week Commencing'!$I$59:$I$66 | I77 |
| Boxh8 | ='Week Commencing'!$J$59:$J$66 | J77 |
| Boxh9 | ='Week Commencing'!$K$59:$K$66 | K77 |
| Boxhi1 | ='Week Commencing'!$C$67:$C$74 | G76:P76, C76:C77 |
| Boxhi10 | ='Week Commencing'!$K$67:$K$74 | K77 |
| Boxhi11 | ='Week Commencing'!$M$67:$M$74 | M77 |
| Boxhi12 | ='Week Commencing'!$N$67:$N$74 | N77 |
| Boxhi13 | ='Week Commencing'!$O$67:$O$74 | O77 |
| Boxhi14 | ='Week Commencing'!$O$67:$O$74 | O77 |
| Boxhi15 | ='Week Commencing'!$P$67:$P$74 | P77 |
| Boxhi2 | ='Week Commencing'!$C$67:$C$74 | G76:P76, C76:C77 |
| Boxhi3 | ='Week Commencing'!$E$67:$E$74 | E76:E77 |
| Boxhi4 | ='Week Commencing'!$F$67:$F$74 | F76:F77 |
| Boxhi5 | ='Week Commencing'!$G$67:$G$74 | G77 |
| Boxhi6 | ='Week Commencing'!$H$67:$H$74 | H77 |
| Boxhi7 | ='Week Commencing'!$I$67:$I$74 | I77 |
| Boxhi8 | ='Week Commencing'!$J$67:$J$74 | J77 |
| Boxhi9 | ='Week Commencing'!$K$67:$K$74 | K77 |
| Boxhill1 | ='Week Commencing'!$C$59:$C$74 | G75:P75, C75, C77 |
| Boxhill10 | ='Week Commencing'!$L$59:$L$74 | L77 |
| Boxhill12 | ='Week Commencing'!$N$59:$N$74 | N77 |
| Boxhill13 | ='Week Commencing'!$O$59:$O$74 | O77 |
| Boxhill14 | ='Week Commencing'!$P$59:$P$74 | P77 |
| BoxHill2 | ='Week Commencing'!$D$59:$D$74 | D75, D77 |
| Boxhill3 | ='Week Commencing'!$E$59:$E$74 | E75, E77 |
| Boxhill4 | ='Week Commencing'!$F$59:$F$74 | F75, F77 |
| Boxhill5 | ='Week Commencing'!$G$59:$G$74 | G77 |
| Boxhill6 | ='Week Commencing'!$H$59:$H$74 | H77 |
| Boxhill7 | ='Week Commencing'!$I$59:$I$74 | I77 |
| Boxhill8 | ='Week Commencing'!$J$59:$J$74 | J77 |
| Boxhill9 | ='Week Commencing'!$K$59:$K$74 | K77 |
| Mitch1 | ='Week Commencing'!$C$33:$C$39 | C40:C41 |
| Mitch10 | ='Week Commencing'!$L$33:$L$39 | L40:L41 |
| Mitch11 | ='Week Commencing'!$M$33:$M$39 | M40:M41 |
| Mitch12 | ='Week Commencing'!$N$33:$N$39 | N40:N41 |
| Mitch13 | ='Week Commencing'!$O$33:$O$39 | O40:O41 |
| Mitch14 | ='Week Commencing'!$P$33:$P$39 | P40:P41 |
| Mitch2 | ='Week Commencing'!$D$33:$D$39 | D40:D41 |
| Mitch3 | ='Week Commencing'!$E$33:$E$39 | E40:E41 |
| Mitch4 | ='Week Commencing'!$F$33:$F$39 | F40:F41 |
| Mitch5 | ='Week Commencing'!$G$33:$G$39 | G40:G41 |
| Mitch6 | ='Week Commencing'!$H$33:$H$39 | H40:H41 |
| Mitch7 | ='Week Commencing'!$I$33:$I$39 | I40:I41 |
| Mitch8 | ='Week Commencing'!$J$33:$J$39 | J40:J41 |
| Mitch9 | ='Week Commencing'!$K$33:$K$39 | K40:K41 |
| Name1 | ='Week Commencing'!$C$8 | C57, C42, C32, C22 |
| Name10 | ='Week Commencing'!$L$8 | L57, L42, L32, L22 |
| Name11 | ='Week Commencing'!$M$8 | M57, M42, M32, M22 |
| Name12 | ='Week Commencing'!$N$8 | N57, N42, N32, N22 |
| Name13 | ='Week Commencing'!$O$8 | O57, O42, O32, O22 |
| Name14 | ='Week Commencing'!$P$8 | P57, P42, P32, P22 |
| Name2 | ='Week Commencing'!$D$8 | D57, D42, D32, D22 |
| Name3 | ='Week Commencing'!$E$8 | E57, E42, E32, E22 |
| Name4 | ='Week Commencing'!$F$8 | F57, F42, F32, F22 |
| Name5 | ='Week Commencing'!$G$8 | G57, G42, G32, G22 |
| Name6 | ='Week Commencing'!$H$8 | H57, H42, H32, H22 |
| Name7 | ='Week Commencing'!$I$8 | I57, I42, I32, I22 |
| Name8 | ='Week Commencing'!$J$8 | J57, J42, J32, J22 |
| Name9 | ='Week Commencing'!$K$8 | K57, K42, K32, K22 |
| Nuna1 | ='Week Commencing'!$C$10:$C$19 | C20:C21 |
| Nuna10 | ='Week Commencing'!$L$10:$L$19 | L20:L21 |
| Nuna11 | ='Week Commencing'!$M$10:$M$19 | M20:M21 |
| Nuna12 | ='Week Commencing'!$N$10:$N$19 | N20:N21 |
| Nuna13 | ='Week Commencing'!$O$10:$O$19 | O20:O21 |
| Nuna14 | ='Week Commencing'!$P$10:$P$19 | P20:P21 |
| Nuna2 | ='Week Commencing'!$D$10:$D$19 | D20:D21 |
| Nuna3 | ='Week Commencing'!$E$10:$E$19 | E20:E21 |
| Nuna4 | ='Week Commencing'!$F$10:$F$19 | F20:F21 |
| Nuna5 | ='Week Commencing'!$G$10:$G$19 | G20:G21 |
| Nuna6 | ='Week Commencing'!$H$10:$H$19 | H20:H21 |
| Nuna7 | ='Week Commencing'!$I$10:$I$19 | I20:I21 |
| Nuna8 | ='Week Commencing'!$J$10:$J$19 | J20:J21 |
| Nuna9 | ='Week Commencing'!$K$10:$K$19 | K20:K21 |
| Umsn1 | ='Week Commencing'!$C$8 | C57, C42, C32, C22 |
| Umsn10 | ='Week Commencing'!$L$8 | L57, L42, L32, L22 |
| Umsn11 | ='Week Commencing'!$M$8 | M57, M42, M32, M22 |
| Umsn12 | ='Week Commencing'!$N$8 | N57, N42, N32, N22 |
| Umsn13 | ='Week Commencing'!$O$8 | O57, O42, O32, O22 |
| Umsn14 | ='Week Commencing'!$P$8 | P57, P42, P32, P22 |
| Umsn2 | ='Week Commencing'!$D$8 | D57, D42, D32, D22 |
| Umsn3 | ='Week Commencing'!$E$8 | E57, E42, E32, E22 |
| Umsn4 | ='Week Commencing'!$F$8 | F57, F42, F32, F22 |
| Umsn5 | ='Week Commencing'!$G$8 | G57, G42, G32, G22 |
| Umsn6 | ='Week Commencing'!$H$8 | H57, H42, H32, H22 |
| Umsn7 | ='Week Commencing'!$I$8 | I57, I42, I32, I22 |
| Umsn8 | ='Week Commencing'!$J$8 | J57, J42, J32, J22 |
| Umsn9 | ='Week Commencing'!$K$8 | K57, K42, K32, K22 |
| Verm1 | ='Week Commencing'!$C$23:$C$29 | C30:C31 |
| Verm10 | ='Week Commencing'!$L$23:$L$29 | L30:L31 |
| Verm12 | ='Week Commencing'!$M$23:$M$29 | M30:M31 |
| Verm13 | ='Week Commencing'!$N$23:$N$29 | N30:N31 |
| Verm14 | ='Week Commencing'!$O$23:$O$29 | O30:O31 |
| verm15 | ='Week Commencing'!$P$23:$P$29 | P30:P31 |
| Verm2 | ='Week Commencing'!$D$23:$D$29 | D30:D31 |
| Verm3 | ='Week Commencing'!$E$23:$E$29 | E30:E31 |
| Verm4 | ='Week Commencing'!$F$23:$F$29 | F30:F31 |
| Verm5 | ='Week Commencing'!$G$23:$G$29 | G30:G31 |
| verm6 | ='Week Commencing'!$H$23:$H$29 | H30:H31 |
| Verm7 | ='Week Commencing'!$I$23:$I$29 | I30:I31 |
| Verm8 | ='Week Commencing'!$J$23:$J$29 | J30:J31 |
| Verm9 | ='Week Commencing'!$K$23:$K$29 | K30:K31 |
Below is 1 of the 5 pages they are all the same format, only difference is the number of columns, this based on how many rows are in each of the groups. Each group has 14 Ranges 1 for each colounm C to P.
The image below is of 1 of the destination sheets. The working code copies into Row 6 values in the range if it finds True, what I want is also for it to copy the Vale in C8 to P8 and C9 to P9 and to place these into the correct group sheet at C2 and E2
