Help with VB code to copy some values from one sheet to another

S

skeeeter56

New Member
Joined
Nov 26, 2016
Messages
42
Office Version
  1. 2019
Platform
  1. 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
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
ABCDEFGHIJKLMNOP
3
4
5
613142312222242
72
8NunawadingTotal Pri.Test 1Test 2Test3Test 4Test 5Test 6Test 7Test 8Test 9Test 10Test 11
9123345678912345678987456123654951
10245824354657687990101112123134
1138462542597693110127144161178195
12478526497295118141164187210233256
135850275685114143172201230259288317
146786286398133168203238273308343378
1578232970111152193234275316357398439
1682023077124171218265312359406453500
1798083184137190243296349402455508561
18109003291150209268327386445504563622
19Boxes343398163228293358423488553618683
20285665104514251805218525652945332537054085   
21SUMPRODUCT(ISTEXT(Verm1)+ISNUMBER(Nuna1))>0TRUETRUETRUETRUEFALSETRUETRUEFALSEFALSEFALSEFALSE   
22VermontTest 1Test 2Test3Test 4Test 5Test 6Test 7Test 8Test 9Test 10Test 11   
23219503453160222295
242285035574651239103
252390036617700256111
262575037660749273119
272685038703798290127
282885039746847307135
292985040789896324143
30259      46205243   1911833
31SUMPRODUCT(ISTEXT(Verm1)+ISNUMBER(Verm1))>0TRUE      FALSEFALSE   TRUEFALSE
32MitchamTest 1Test 2Test3Test 4Test 5Test 6Test 7Test 8Test 9Test 10Test 11   
333145034105176247
343280035112189266
353380036119202285
363480037126215304
373580038133228323
383675039140241342
393735040147254361
4025988215052128          
41SUMPRODUCT(ISTEXT(Verm1)+ISNUMBER(Verm1))>0TRUETRUETRUETRUE          
42BlackburnTest 1Test 2Test3Test 4Test 5Test 6Test 7Test 8Test 9Test 10Test 11   
434145024354657687990101112123134
44424502542597693110127144161178195
454370026497295118141164187210233256
4644850275685114143172201230259288317
4745950286398133168203238273308343378
48469002970111152193234275316357398439
49477503077124171218265312359406453500
50489503184137190243296349402455508561
51499003291150209268327386445504563622
52504003398163228293358423488553618683
535195034105176247318389460531602673744
545295035112189266343420497574651728805
55354882141019382466299435224050457851065634   
56TRUETRUETRUETRUEFALSETRUETRUEFALSEFALSEFALSEFALSE   
57Box HillTest 1Test 2Test3Test 4Test 5Test 6Test 7Test 8Test 9Test 10Test 11   
596160030124359406453500
606275031137402455508561
616380032150445504563622
626485033163488553618683
636570034176531602673744
646665035189574651728805
656720036202617700783866
6668110037215660749838927
676993038228703798893988
6870800392417468479481049
69718604025478989610031110
70729504126783294510581171
71737504228087599411131232
727440043293918104311681293
737580044306961109212231354
7476300453191004114112781415
75TRUE TRUE FALSETRUETRUEFALSEFALSEFALSEFALSEFALSETRUEFALSE
76TRUE TRUE FALSETRUETRUEFALSEFALSEFALSEFALSEFALSETRUEFALSE
77TRUE TRUE    FALSEFALSEFALSEFALSE   
Week Commencing
Cell Formulas
RangeFormula
C20:P20C20=SUM(C10:C19)
C21C21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna1))<>ROWS(Nuna1)),C6<>2,0)
D21D21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna2))<>ROWS(Nuna2)),D6<>2,0)
E21E21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna3))<>ROWS(Nuna3)),E6<>2,0)
F21F21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna4))<>ROWS(Nuna4)),F6<>2,0)
G21G21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna5))<>ROWS(Nuna5)),G6<>2,0)
H21H21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna6))<>ROWS(Nuna6)),H6<>2,0)
I21I21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna7))<>ROWS(Nuna7)),I6<>2,0)
J21J21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna8))<>ROWS(Nuna8)),J6<>2,0)
K21K21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna9))<>ROWS(Nuna9)),K6<>2,0)
L21L21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna10))<>ROWS(Nuna10)),L6<>2,0)
M21M21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna11))<>ROWS(Nuna11)),M6<>2,0)
N21N21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna12))<>ROWS(Nuna12)),N6<>2,0)
O21O21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna13))<>ROWS(Nuna13)),O6<>2,0)
P21P21=IF(AND(SUMPRODUCT(--ISBLANK(Nuna14))<>ROWS(Nuna14)),P6<>2,0)
C22:P22C22=C8
C30:P30,C40:P40C30=SUM(C23:C29)
C31C31=IF(AND(SUMPRODUCT(--ISBLANK(Verm1))<>ROWS(Verm1)),C6<>2,0)
D31D31=IF(AND(SUMPRODUCT(--ISBLANK(Verm2))<>ROWS(Verm2)),D6<>2,0)
E31E31=IF(AND(SUMPRODUCT(--ISBLANK(Verm3))<>ROWS(Verm3)),E6<>2,0)
F31F31=IF(AND(SUMPRODUCT(--ISBLANK(Verm4))<>ROWS(Verm4)),F6<>2,0)
G31G31=IF(AND(SUMPRODUCT(--ISBLANK(Verm5))<>ROWS(Verm5)),G6<>2,0)
H31H31=IF(AND(SUMPRODUCT(--ISBLANK(verm6))<>ROWS(verm6)),H6<>2,0)
I31I31=IF(AND(SUMPRODUCT(--ISBLANK(Verm7))<>ROWS(Verm7)),I6<>2,0)
J31J31=IF(AND(SUMPRODUCT(--ISBLANK(Verm8))<>ROWS(Verm8)),J6<>2,0)
K31K31=IF(AND(SUMPRODUCT(--ISBLANK(Verm9))<>ROWS(Verm9)),K6<>2,0)
L31L31=IF(AND(SUMPRODUCT(--ISBLANK(Verm10))<>ROWS(Verm10)),L6<>2,0)
M31M31=IF(AND(SUMPRODUCT(--ISBLANK(Verm12))<>ROWS(Verm12)),M6<>2,0)
N31N31=IF(AND(SUMPRODUCT(--ISBLANK(Verm13))<>ROWS(Verm13)),N6<>2,0)
O31O31=IF(AND(SUMPRODUCT(--ISBLANK(Verm14))<>ROWS(Verm14)),O6<>2,0)
P31P31=IF(AND(SUMPRODUCT(--ISBLANK(verm15))<>ROWS(verm15)),P6<>2,0)
C32:P32C32=C8
C41C41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch1))<>ROWS(Mitch1)),C6<>2,0)
D41D41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch2))<>ROWS(Mitch2)),D6<>2,0)
E41E41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch3))<>ROWS(Mitch3)),E6<>2,0)
F41F41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch4))<>ROWS(Mitch4)),F6<>2,0)
G41G41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch5))<>ROWS(Mitch5)),G6<>2,0)
H41H41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch6))<>ROWS(Mitch6)),H6<>2,0)
I41I41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch7))<>ROWS(Mitch7)),I6<>2,0)
J41J41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch8))<>ROWS(Mitch8)),J6<>2,0)
K41K41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch9))<>ROWS(Mitch9)),K6<>2,0)
L41L41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch10))<>ROWS(Mitch10)),L6<>2,0)
M41M41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch11))<>ROWS(Mitch11)),M6<>2,0)
N41N41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch12))<>ROWS(Mitch12)),N6<>2,0)
O41O41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch13))<>ROWS(Mitch13)),O6<>2,0)
P41P41=IF(AND(SUMPRODUCT(--ISBLANK(Mitch14))<>ROWS(Mitch14)),P6<>2,0)
C42:P42C42=C8
C55:P55D55=SUM(D43:D54)
D56D56=IF(AND(SUMPRODUCT(--ISBLANK(Black2))<>ROWS(Black2)),D6<>2,0)
E56E56=IF(AND(SUMPRODUCT(--ISBLANK(Black3))<>ROWS(Black3)),E6<>2,0)
F56F56=IF(AND(SUMPRODUCT(--ISBLANK(Black4))<>ROWS(Black4)),F6<>2,0)
G56G56=IF(AND(SUMPRODUCT(--ISBLANK(Black5))<>ROWS(Black5)),G6<>2,0)
H56H56=IF(AND(SUMPRODUCT(--ISBLANK(Black6))<>ROWS(Black6)),H6<>2,0)
I56I56=IF(AND(SUMPRODUCT(--ISBLANK(Black7))<>ROWS(Black7)),I6<>2,0)
J56J56=IF(AND(SUMPRODUCT(--ISBLANK(Black8))<>ROWS(Black8)),J6<>2,0)
K56K56=IF(AND(SUMPRODUCT(--ISBLANK(Black9))<>ROWS(Black9)),K6<>2,0)
L56L56=IF(AND(SUMPRODUCT(--ISBLANK(Black10))<>ROWS(Black10)),L6<>2,0)
M56M56=IF(AND(SUMPRODUCT(--ISBLANK(Black11))<>ROWS(Black11)),M6<>2,0)
N56N56=IF(AND(SUMPRODUCT(--ISBLANK(Black12))<>ROWS(Black12)),N6<>2,0)
O56O56=IF(AND(SUMPRODUCT(--ISBLANK(Black13))<>ROWS(Black13)),O6<>2,0)
P56P56=IF(AND(SUMPRODUCT(--ISBLANK(Black14))<>ROWS(Black14)),P6<>2,0)
C57:P57D57=D8
C56C56=IF(AND(SUMPRODUCT(--ISBLANK(Black1))<>ROWS(Black1)),C6<>2,0)
C75,G75:P75C75=IF(AND(SUMPRODUCT(--ISBLANK(Boxh1))<>ROWS(Boxh1)),C6<>2,0)
D75D75=IF(AND(SUMPRODUCT(--ISBLANK(Boxh2))<>ROWS(Boxh2)),D6<>2,0)
E75E75=IF(AND(SUMPRODUCT(--ISBLANK(Boxh3))<>ROWS(Boxh3)),E6<>2,0)
F75F75=IF(AND(SUMPRODUCT(--ISBLANK(Boxh4))<>ROWS(Boxh4)),F6<>2,0)
C76,G76:P76C76=IF(AND(SUMPRODUCT(--ISBLANK(Boxhi1))<>ROWS(Boxhi1)),C6<>2,0)
D76D76=IF(AND(SUMPRODUCT(--ISBLANK(D67:D74))<>ROWS(D67:D74)),D6<>2,0)
E76E76=IF(AND(SUMPRODUCT(--ISBLANK(Boxhi3))<>ROWS(Boxhi3)),E6<>2,0)
F76F76=IF(AND(SUMPRODUCT(--ISBLANK(Boxhi4))<>ROWS(Boxhi4)),F6<>2,0)
C77C77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill1))<>ROWS(Boxhill1)),C6<>2,0)
D77D77=IF(AND(SUMPRODUCT(--ISBLANK(BoxHill2))<>ROWS(BoxHill2)),D6<>2,0)
E77E77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill3))<>ROWS(Boxhill3)),E6<>2,0)
F77F77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill4))<>ROWS(Boxhill4)),F6<>2,0)
G77G77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill5))<>ROWS(Boxhill5)),G6<>2,0)
H77H77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill6))<>ROWS(Boxhill6)),H6<>2,0)
I77I77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill7))<>ROWS(Boxhill7)),I6<>2,0)
J77J77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill8))<>ROWS(Boxhill8)),J6<>2,0)
K77K77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill9))<>ROWS(Boxhill9)),K6<>2,0)
L77L77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill10))<>ROWS(Boxhill10)),L6<>2,0)
M77M77=IF(AND(SUMPRODUCT(--ISBLANK(M59:M74))<>ROWS(M59:M74)),M6<>2,0)
N77N77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill12))<>ROWS(Boxhill12)),N6<>2,0)
O77O77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill13))<>ROWS(Boxhill13)),O6<>2,0)
P77P77=IF(AND(SUMPRODUCT(--ISBLANK(Boxhill14))<>ROWS(Boxhill14)),P6<>2,0)
Named Ranges
NameRefers ToCells
Black1='Week Commencing'!$C$43:$C$54C55:C56
Black10='Week Commencing'!$L$43:$L$54L55:L56
Black11='Week Commencing'!$M$43:$M$54M55:M56
Black12='Week Commencing'!$N$43:$N$54N55:N56
Black13='Week Commencing'!$O$43:$O$54O55:O56
Black14='Week Commencing'!$P$43:$P$54P55:P56
Black2='Week Commencing'!$D$43:$D$54D55:D56
Black3='Week Commencing'!$E$43:$E$54E55:E56
Black4='Week Commencing'!$F$43:$F$54F55:F56
Black5='Week Commencing'!$G$43:$G$54G55:G56
Black6='Week Commencing'!$H$43:$H$54H55:H56
Black7='Week Commencing'!$I$43:$I$54I55:I56
Black8='Week Commencing'!$J$43:$J$54J55:J56
Black9='Week Commencing'!$K$43:$K$54K55:K56
BNlack12='Week Commencing'!$N$43:$N$54N55:N56
Boxh1='Week Commencing'!$C$59:$C$66G75:P75, C75, C77
Boxh10='Week Commencing'!$L$59:$L$66L77
Boxh11='Week Commencing'!$M$59:$M$66M77
Boxh12='Week Commencing'!$N$59:$N$66N77
Boxh13='Week Commencing'!$N$59:$N$66N77
Boxh14='Week Commencing'!$O$59:$O$66O77
Boxh15='Week Commencing'!$P$59:$P$66P77
Boxh2='Week Commencing'!$D$59:$D$66D75, D77
Boxh3='Week Commencing'!$E$59:$E$66E75, E77
Boxh4='Week Commencing'!$F$59:$F$66F75, F77
Boxh5='Week Commencing'!$G$59:$G$66G77
Boxh6='Week Commencing'!$H$59:$H$66H77
Boxh7='Week Commencing'!$I$59:$I$66I77
Boxh8='Week Commencing'!$J$59:$J$66J77
Boxh9='Week Commencing'!$K$59:$K$66K77
Boxhi1='Week Commencing'!$C$67:$C$74G76:P76, C76:C77
Boxhi10='Week Commencing'!$K$67:$K$74K77
Boxhi11='Week Commencing'!$M$67:$M$74M77
Boxhi12='Week Commencing'!$N$67:$N$74N77
Boxhi13='Week Commencing'!$O$67:$O$74O77
Boxhi14='Week Commencing'!$O$67:$O$74O77
Boxhi15='Week Commencing'!$P$67:$P$74P77
Boxhi2='Week Commencing'!$C$67:$C$74G76:P76, C76:C77
Boxhi3='Week Commencing'!$E$67:$E$74E76:E77
Boxhi4='Week Commencing'!$F$67:$F$74F76:F77
Boxhi5='Week Commencing'!$G$67:$G$74G77
Boxhi6='Week Commencing'!$H$67:$H$74H77
Boxhi7='Week Commencing'!$I$67:$I$74I77
Boxhi8='Week Commencing'!$J$67:$J$74J77
Boxhi9='Week Commencing'!$K$67:$K$74K77
Boxhill1='Week Commencing'!$C$59:$C$74G75:P75, C75, C77
Boxhill10='Week Commencing'!$L$59:$L$74L77
Boxhill12='Week Commencing'!$N$59:$N$74N77
Boxhill13='Week Commencing'!$O$59:$O$74O77
Boxhill14='Week Commencing'!$P$59:$P$74P77
BoxHill2='Week Commencing'!$D$59:$D$74D75, D77
Boxhill3='Week Commencing'!$E$59:$E$74E75, E77
Boxhill4='Week Commencing'!$F$59:$F$74F75, F77
Boxhill5='Week Commencing'!$G$59:$G$74G77
Boxhill6='Week Commencing'!$H$59:$H$74H77
Boxhill7='Week Commencing'!$I$59:$I$74I77
Boxhill8='Week Commencing'!$J$59:$J$74J77
Boxhill9='Week Commencing'!$K$59:$K$74K77
Mitch1='Week Commencing'!$C$33:$C$39C40:C41
Mitch10='Week Commencing'!$L$33:$L$39L40:L41
Mitch11='Week Commencing'!$M$33:$M$39M40:M41
Mitch12='Week Commencing'!$N$33:$N$39N40:N41
Mitch13='Week Commencing'!$O$33:$O$39O40:O41
Mitch14='Week Commencing'!$P$33:$P$39P40:P41
Mitch2='Week Commencing'!$D$33:$D$39D40:D41
Mitch3='Week Commencing'!$E$33:$E$39E40:E41
Mitch4='Week Commencing'!$F$33:$F$39F40:F41
Mitch5='Week Commencing'!$G$33:$G$39G40:G41
Mitch6='Week Commencing'!$H$33:$H$39H40:H41
Mitch7='Week Commencing'!$I$33:$I$39I40:I41
Mitch8='Week Commencing'!$J$33:$J$39J40:J41
Mitch9='Week Commencing'!$K$33:$K$39K40:K41
Name1='Week Commencing'!$C$8C57, C42, C32, C22
Name10='Week Commencing'!$L$8L57, L42, L32, L22
Name11='Week Commencing'!$M$8M57, M42, M32, M22
Name12='Week Commencing'!$N$8N57, N42, N32, N22
Name13='Week Commencing'!$O$8O57, O42, O32, O22
Name14='Week Commencing'!$P$8P57, P42, P32, P22
Name2='Week Commencing'!$D$8D57, D42, D32, D22
Name3='Week Commencing'!$E$8E57, E42, E32, E22
Name4='Week Commencing'!$F$8F57, F42, F32, F22
Name5='Week Commencing'!$G$8G57, G42, G32, G22
Name6='Week Commencing'!$H$8H57, H42, H32, H22
Name7='Week Commencing'!$I$8I57, I42, I32, I22
Name8='Week Commencing'!$J$8J57, J42, J32, J22
Name9='Week Commencing'!$K$8K57, K42, K32, K22
Nuna1='Week Commencing'!$C$10:$C$19C20:C21
Nuna10='Week Commencing'!$L$10:$L$19L20:L21
Nuna11='Week Commencing'!$M$10:$M$19M20:M21
Nuna12='Week Commencing'!$N$10:$N$19N20:N21
Nuna13='Week Commencing'!$O$10:$O$19O20:O21
Nuna14='Week Commencing'!$P$10:$P$19P20:P21
Nuna2='Week Commencing'!$D$10:$D$19D20:D21
Nuna3='Week Commencing'!$E$10:$E$19E20:E21
Nuna4='Week Commencing'!$F$10:$F$19F20:F21
Nuna5='Week Commencing'!$G$10:$G$19G20:G21
Nuna6='Week Commencing'!$H$10:$H$19H20:H21
Nuna7='Week Commencing'!$I$10:$I$19I20:I21
Nuna8='Week Commencing'!$J$10:$J$19J20:J21
Nuna9='Week Commencing'!$K$10:$K$19K20:K21
Umsn1='Week Commencing'!$C$8C57, C42, C32, C22
Umsn10='Week Commencing'!$L$8L57, L42, L32, L22
Umsn11='Week Commencing'!$M$8M57, M42, M32, M22
Umsn12='Week Commencing'!$N$8N57, N42, N32, N22
Umsn13='Week Commencing'!$O$8O57, O42, O32, O22
Umsn14='Week Commencing'!$P$8P57, P42, P32, P22
Umsn2='Week Commencing'!$D$8D57, D42, D32, D22
Umsn3='Week Commencing'!$E$8E57, E42, E32, E22
Umsn4='Week Commencing'!$F$8F57, F42, F32, F22
Umsn5='Week Commencing'!$G$8G57, G42, G32, G22
Umsn6='Week Commencing'!$H$8H57, H42, H32, H22
Umsn7='Week Commencing'!$I$8I57, I42, I32, I22
Umsn8='Week Commencing'!$J$8J57, J42, J32, J22
Umsn9='Week Commencing'!$K$8K57, K42, K32, K22
Verm1='Week Commencing'!$C$23:$C$29C30:C31
Verm10='Week Commencing'!$L$23:$L$29L30:L31
Verm12='Week Commencing'!$M$23:$M$29M30:M31
Verm13='Week Commencing'!$N$23:$N$29N30:N31
Verm14='Week Commencing'!$O$23:$O$29O30:O31
verm15='Week Commencing'!$P$23:$P$29P30:P31
Verm2='Week Commencing'!$D$23:$D$29D30:D31
Verm3='Week Commencing'!$E$23:$E$29E30:E31
Verm4='Week Commencing'!$F$23:$F$29F30:F31
Verm5='Week Commencing'!$G$23:$G$29G30:G31
verm6='Week Commencing'!$H$23:$H$29H30:H31
Verm7='Week Commencing'!$I$23:$I$29I30:I31
Verm8='Week Commencing'!$J$23:$J$29J30:J31
Verm9='Week Commencing'!$K$23:$K$29K30: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
capa.JPG
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You must log in or register to reply here.

Similar threads

J
  • Solved
Row Ranking Not Working Correctly
Replies
3
Views
195
Fluff
Fluff
K
  • Question
conditional formatting question
Replies
2
Views
149
kerm007
K
A
  • Question
Chart Timeline Help
Replies
4
Views
285
antrawson
A
willow1985
  • Question
Assistance with Spreadsheet that is constantly crashing/over complicated formulas
Replies
1
Views
381
willow1985
willow1985
VinceF
  • Question
Circular Reference Error
Replies
8
Views
374
VinceF
VinceF

Forum statistics

Threads
1,214,522
Messages
6,120,020
Members
448,938
Latest member
Aaliya13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top