Having an issue with a macro

M

mcgrupp

Board Regular
Joined
Jan 15, 2015
Messages
66
I unfortunately do not have the VBA training necessary to make a macro from scratch so I was stuck recording this macro. The problem I am having is that some of the data in columns BA to BF goes "missing" when the macro processes. This part is towards the end of the macro. If I am paste just the values of these dependent columns, why would this even occur? I have recorded plenty of macros and never seen this happen before. I tried re-recording the macro and got the same result.
Sub FishingForEmployeeIDs()
'
' FishingForEmployeeIDs Macro
'

'
Sheets("Sheet3").Select
Columns("B:B").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Columns("H:H").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Columns("I:I").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-2],""-"","" "")"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:K2"), Type:=xlFillDefault
Range("J2:K2").Select
Range("J2:K2").Select
ActiveWindow.ScrollColumn = 8
Selection.AutoFill Destination:=Range("J2:K10000"), Type:=xlFillDefault
Range("J2:K10000").Select
ActiveWindow.ScrollRow = 9899
ActiveWindow.ScrollRow = 9884
ActiveWindow.ScrollRow = 9854
ActiveWindow.ScrollRow = 9847
ActiveWindow.ScrollRow = 9780
ActiveWindow.ScrollRow = 9646
ActiveWindow.ScrollRow = 9482
ActiveWindow.ScrollRow = 9326
ActiveWindow.ScrollRow = 9125
ActiveWindow.ScrollRow = 8872
ActiveWindow.ScrollRow = 8582
ActiveWindow.ScrollRow = 8314
ActiveWindow.ScrollRow = 8046
ActiveWindow.ScrollRow = 7696
ActiveWindow.ScrollRow = 7347
ActiveWindow.ScrollRow = 6952
ActiveWindow.ScrollRow = 6521
ActiveWindow.ScrollRow = 6104
ActiveWindow.ScrollRow = 5680
ActiveWindow.ScrollRow = 5196
ActiveWindow.ScrollRow = 4772
ActiveWindow.ScrollRow = 4370
ActiveWindow.ScrollRow = 3968
ActiveWindow.ScrollRow = 3581
ActiveWindow.ScrollRow = 3224
ActiveWindow.ScrollRow = 2860
ActiveWindow.ScrollRow = 2525
ActiveWindow.ScrollRow = 2227
ActiveWindow.ScrollRow = 1929
ActiveWindow.ScrollRow = 1661
ActiveWindow.ScrollRow = 1408
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 977
ActiveWindow.ScrollRow = 754
ActiveWindow.ScrollRow = 575
ActiveWindow.ScrollRow = 441
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 2
Columns("J:K").Select
Range("K1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("L2").Select
ActiveCell.FormulaR1C1 = "=TRIM(TRIM(TRIM(TRIM(TRIM(RC[-2])))))"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]="""","""",IF(MID(TRIM(RC[-3]),LEN(TRIM(RC[-3]))-1,1)="" "",PROPER(LEFT(TRIM(RC[-3]),LEN(TRIM(RC[-3]))-1)),PROPER(TRIM(RC[-3]))))"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=TRIM(TRIM(TRIM(TRIM(TRIM(RC[1])))))"
Range("L2:N2").Select
Selection.AutoFill Destination:=Range("L2:N10000"), Type:=xlFillDefault
Range("L2:N10000").Select
ActiveWindow.ScrollRow = 9907
ActiveWindow.ScrollRow = 9892
ActiveWindow.ScrollRow = 9884
ActiveWindow.ScrollRow = 9817
ActiveWindow.ScrollRow = 9668
ActiveWindow.ScrollRow = 9415
ActiveWindow.ScrollRow = 9081
ActiveWindow.ScrollRow = 8731
ActiveWindow.ScrollRow = 8344
ActiveWindow.ScrollRow = 7927
ActiveWindow.ScrollRow = 7473
ActiveWindow.ScrollRow = 7004
ActiveWindow.ScrollRow = 6506
ActiveWindow.ScrollRow = 6015
ActiveWindow.ScrollRow = 5516
ActiveWindow.ScrollRow = 5047
ActiveWindow.ScrollRow = 4586
ActiveWindow.ScrollRow = 4206
ActiveWindow.ScrollRow = 3864
ActiveWindow.ScrollRow = 3537
ActiveWindow.ScrollRow = 3232
ActiveWindow.ScrollRow = 2926
ActiveWindow.ScrollRow = 2659
ActiveWindow.ScrollRow = 2406
ActiveWindow.ScrollRow = 2182
ActiveWindow.ScrollRow = 1974
ActiveWindow.ScrollRow = 1751
ActiveWindow.ScrollRow = 1557
ActiveWindow.ScrollRow = 1416
ActiveWindow.ScrollRow = 1267
ActiveWindow.ScrollRow = 1141
ActiveWindow.ScrollRow = 1014
ActiveWindow.ScrollRow = 902
ActiveWindow.ScrollRow = 806
ActiveWindow.ScrollRow = 731
ActiveWindow.ScrollRow = 649
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 515
ActiveWindow.ScrollRow = 448
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 300
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 2
Columns("L:N").Select
Range("N1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Application.CutCopyMode = False
Range("O2").Select
ActiveCell.FormulaR1C1 = "=TRIM(TRIM(TRIM(TRIM(TRIM(RC[1])))))"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-5])"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=TRIM(TRIM(TRIM(TRIM(TRIM(RC[-13])))))"
Range("O2:Q2").Select
Range("Q2").Activate
Selection.AutoFill Destination:=Range("O2:Q10000"), Type:=xlFillDefault
Range("O2:Q10000").Select
ActiveWindow.ScrollRow = 9907
ActiveWindow.ScrollRow = 9899
ActiveWindow.ScrollRow = 9869
ActiveWindow.ScrollRow = 9758
ActiveWindow.ScrollRow = 9594
ActiveWindow.ScrollRow = 9356
ActiveWindow.ScrollRow = 9073
ActiveWindow.ScrollRow = 8738
ActiveWindow.ScrollRow = 8344
ActiveWindow.ScrollRow = 7890
ActiveWindow.ScrollRow = 7473
ActiveWindow.ScrollRow = 7042
ActiveWindow.ScrollRow = 6565
ActiveWindow.ScrollRow = 6074
ActiveWindow.ScrollRow = 5620
ActiveWindow.ScrollRow = 5144
ActiveWindow.ScrollRow = 4698
ActiveWindow.ScrollRow = 4259
ActiveWindow.ScrollRow = 3872
ActiveWindow.ScrollRow = 3507
ActiveWindow.ScrollRow = 3165
ActiveWindow.ScrollRow = 2889
ActiveWindow.ScrollRow = 2584
ActiveWindow.ScrollRow = 2316
ActiveWindow.ScrollRow = 2086
ActiveWindow.ScrollRow = 1892
ActiveWindow.ScrollRow = 1684
ActiveWindow.ScrollRow = 1550
ActiveWindow.ScrollRow = 1423
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1274
ActiveWindow.ScrollRow = 1208
ActiveWindow.ScrollRow = 1170
ActiveWindow.ScrollRow = 1141
ActiveWindow.ScrollRow = 1133
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 1141
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1103
ActiveWindow.ScrollRow = 1059
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 947
ActiveWindow.ScrollRow = 865
ActiveWindow.ScrollRow = 783
ActiveWindow.ScrollRow = 701
ActiveWindow.ScrollRow = 627
ActiveWindow.ScrollRow = 560
ActiveWindow.ScrollRow = 486
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 2
Columns("O:Q").Select
Range("Q1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.Copy
Columns("U:U").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("W:W").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("A:B").Select
Range("B1").Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
Columns("S:T").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
Columns("F:G").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
Columns("Y:Z").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
Columns("V:V").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
Columns("M:M").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
Columns("AA:AA").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
Columns("O:O").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
Columns("AB:AB").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISERROR(VLOOKUP(RC[-2],C20:C22,3,0))=TRUE,""ZZ Name Not Found"",VLOOKUP(RC[-2],C20:C22,3,0)))"
Range("AC2").Select
Selection.AutoFill Destination:=Range("AC2:AC10002"), Type:=xlFillDefault
Range("AC2:AC10002").Select
ActiveWindow.ScrollRow = 9886
ActiveWindow.ScrollRow = 9842
ActiveWindow.ScrollRow = 9782
ActiveWindow.ScrollRow = 9670
ActiveWindow.ScrollRow = 9536
ActiveWindow.ScrollRow = 9328
ActiveWindow.ScrollRow = 9075
ActiveWindow.ScrollRow = 8800
ActiveWindow.ScrollRow = 8502
ActiveWindow.ScrollRow = 8219
ActiveWindow.ScrollRow = 7936
ActiveWindow.ScrollRow = 7638
ActiveWindow.ScrollRow = 7378
ActiveWindow.ScrollRow = 7073
ActiveWindow.ScrollRow = 6783
ActiveWindow.ScrollRow = 6477
ActiveWindow.ScrollRow = 6172
ActiveWindow.ScrollRow = 5897
ActiveWindow.ScrollRow = 5592
ActiveWindow.ScrollRow = 5383
ActiveWindow.ScrollRow = 5138
ActiveWindow.ScrollRow = 4907
ActiveWindow.ScrollRow = 4698
ActiveWindow.ScrollRow = 4490
ActiveWindow.ScrollRow = 4259
ActiveWindow.ScrollRow = 4073
ActiveWindow.ScrollRow = 3872
ActiveWindow.ScrollRow = 3701
ActiveWindow.ScrollRow = 3515
ActiveWindow.ScrollRow = 3359
ActiveWindow.ScrollRow = 3210
ActiveWindow.ScrollRow = 3113
ActiveWindow.ScrollRow = 3039
ActiveWindow.ScrollRow = 2949
ActiveWindow.ScrollRow = 2868
ActiveWindow.ScrollRow = 2793
ActiveWindow.ScrollRow = 2734
ActiveWindow.ScrollRow = 2674
ActiveWindow.ScrollRow = 2629
ActiveWindow.ScrollRow = 2592
ActiveWindow.ScrollRow = 2585
ActiveWindow.ScrollRow = 2577
ActiveWindow.ScrollRow = 2570
ActiveWindow.ScrollRow = 2533
ActiveWindow.ScrollRow = 2481
ActiveWindow.ScrollRow = 2443
ActiveWindow.ScrollRow = 2406
ActiveWindow.ScrollRow = 2287
ActiveWindow.ScrollRow = 2160
ActiveWindow.ScrollRow = 2041
ActiveWindow.ScrollRow = 1959
ActiveWindow.ScrollRow = 1863
ActiveWindow.ScrollRow = 1766
ActiveWindow.ScrollRow = 1692
ActiveWindow.ScrollRow = 1617
ActiveWindow.ScrollRow = 1543
ActiveWindow.ScrollRow = 1476
ActiveWindow.ScrollRow = 1409
ActiveWindow.ScrollRow = 1357
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow = 1275
ActiveWindow.ScrollRow = 1245
ActiveWindow.ScrollRow = 1208
ActiveWindow.ScrollRow = 1178
ActiveWindow.ScrollRow = 1141
ActiveWindow.ScrollRow = 1118
ActiveWindow.ScrollRow = 1096
ActiveWindow.ScrollRow = 1074
ActiveWindow.ScrollRow = 1051
ActiveWindow.ScrollRow = 1029
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 977
ActiveWindow.ScrollRow = 970
ActiveWindow.ScrollRow = 947
ActiveWindow.ScrollRow = 925
ActiveWindow.ScrollRow = 903
ActiveWindow.ScrollRow = 873
ActiveWindow.ScrollRow = 850
ActiveWindow.ScrollRow = 828
ActiveWindow.ScrollRow = 798
ActiveWindow.ScrollRow = 776
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 717
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 650
ActiveWindow.ScrollRow = 620
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 545
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 449
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 352
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 329
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 2
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC[-1]=""ZZ NAME NOT FOUND"",""NAME NOT FOUND"",IF(COUNTIF(C[-1],RC[-1])>1,""MULTIPLE EMPLOYEES WITH SAME NAME-VERIFY THIS EMPLOYEE ID"","""")))"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD10002"), Type:=xlFillDefault
Range("AD2:AD10002").Select
ActiveWindow.ScrollRow = 9909
ActiveWindow.ScrollRow = 9827
ActiveWindow.ScrollRow = 9737
ActiveWindow.ScrollRow = 9626
ActiveWindow.ScrollRow = 9484
ActiveWindow.ScrollRow = 9321
ActiveWindow.ScrollRow = 9090
ActiveWindow.ScrollRow = 8800
ActiveWindow.ScrollRow = 8532
ActiveWindow.ScrollRow = 8182
ActiveWindow.ScrollRow = 7787
ActiveWindow.ScrollRow = 7438
ActiveWindow.ScrollRow = 7088
ActiveWindow.ScrollRow = 6716
ActiveWindow.ScrollRow = 6321
ActiveWindow.ScrollRow = 5912
ActiveWindow.ScrollRow = 5480
ActiveWindow.ScrollRow = 5041
ActiveWindow.ScrollRow = 4654
ActiveWindow.ScrollRow = 4259
ActiveWindow.ScrollRow = 3887
ActiveWindow.ScrollRow = 3515
ActiveWindow.ScrollRow = 3165
ActiveWindow.ScrollRow = 2853
ActiveWindow.ScrollRow = 2570
ActiveWindow.ScrollRow = 2317
ActiveWindow.ScrollRow = 2131
ActiveWindow.ScrollRow = 1967
ActiveWindow.ScrollRow = 1855
ActiveWindow.ScrollRow = 1759
ActiveWindow.ScrollRow = 1699
ActiveWindow.ScrollRow = 1632
ActiveWindow.ScrollRow = 1587
ActiveWindow.ScrollRow = 1580
ActiveWindow.ScrollRow = 1543
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1468
ActiveWindow.ScrollRow = 1342
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1066
ActiveWindow.ScrollRow = 940
ActiveWindow.ScrollRow = 821
ActiveWindow.ScrollRow = 724
ActiveWindow.ScrollRow = 642
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 2
Columns("S:AD").Select
Range("AD1").Activate
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range( _
"AD2:AD10002"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("S1:AD10002")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
Columns("AD:AD").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
Columns("AE:AE").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
Columns("AF:AF").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 30
ActiveWindow.SmallScroll ToRight:=-2
Columns("AC:AC").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
Columns("AH:AH").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
Columns("AB:AB").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
Columns("AI:AI").Select
ActiveSheet.Paste
Range("AG2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=””,””,IF(ISERROR((VLOOKUP(RC[-2],C34:C35,2,0)))=TRUE,""STILL AVAILABLE"",""""))"
Range("AG2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF((ISERROR(VLOOKUP(RC[-2],C34:C35,2,0)))=TRUE,""STILL AVAILABLE"",""""))"
Range("AG2").Select
Selection.AutoFill Destination:=Range("AG2:AG10002"), Type:=xlFillDefault
Range("AG2:AG10002").Select
ActiveWindow.ScrollRow = 9909
ActiveWindow.ScrollRow = 9894
ActiveWindow.ScrollRow = 9871
ActiveWindow.ScrollRow = 9834
ActiveWindow.ScrollRow = 9789
ActiveWindow.ScrollRow = 9730
ActiveWindow.ScrollRow = 9655
ActiveWindow.ScrollRow = 9566
ActiveWindow.ScrollRow = 9417
ActiveWindow.ScrollRow = 9261
ActiveWindow.ScrollRow = 9090
ActiveWindow.ScrollRow = 8867
ActiveWindow.ScrollRow = 8651
ActiveWindow.ScrollRow = 8435
ActiveWindow.ScrollRow = 8174
ActiveWindow.ScrollRow = 7906
ActiveWindow.ScrollRow = 7676
ActiveWindow.ScrollRow = 7445
ActiveWindow.ScrollRow = 7237
ActiveWindow.ScrollRow = 7021
ActiveWindow.ScrollRow = 6805
ActiveWindow.ScrollRow = 6604
ActiveWindow.ScrollRow = 6388
ActiveWindow.ScrollRow = 6202
ActiveWindow.ScrollRow = 5971
ActiveWindow.ScrollRow = 5770
ActiveWindow.ScrollRow = 5562
ActiveWindow.ScrollRow = 5331
ActiveWindow.ScrollRow = 5145
ActiveWindow.ScrollRow = 4974
ActiveWindow.ScrollRow = 4810
ActiveWindow.ScrollRow = 4646
ActiveWindow.ScrollRow = 4498
ActiveWindow.ScrollRow = 4334
ActiveWindow.ScrollRow = 4163
ActiveWindow.ScrollRow = 3999
ActiveWindow.ScrollRow = 3850
ActiveWindow.ScrollRow = 3664
ActiveWindow.ScrollRow = 3500
ActiveWindow.ScrollRow = 3351
ActiveWindow.ScrollRow = 3173
ActiveWindow.ScrollRow = 2994
ActiveWindow.ScrollRow = 2838
ActiveWindow.ScrollRow = 2719
ActiveWindow.ScrollRow = 2585
ActiveWindow.ScrollRow = 2495
ActiveWindow.ScrollRow = 2399
ActiveWindow.ScrollRow = 2339
ActiveWindow.ScrollRow = 2257
ActiveWindow.ScrollRow = 2183
ActiveWindow.ScrollRow = 2108
ActiveWindow.ScrollRow = 2071
ActiveWindow.ScrollRow = 2034
ActiveWindow.ScrollRow = 1982
ActiveWindow.ScrollRow = 1878
ActiveWindow.ScrollRow = 1766
ActiveWindow.ScrollRow = 1654
ActiveWindow.ScrollRow = 1505
ActiveWindow.ScrollRow = 1401
ActiveWindow.ScrollRow = 1297
ActiveWindow.ScrollRow = 1156
ActiveWindow.ScrollRow = 1074
ActiveWindow.ScrollRow = 999
ActiveWindow.ScrollRow = 917
ActiveWindow.ScrollRow = 843
ActiveWindow.ScrollRow = 791
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 642
ActiveWindow.ScrollRow = 612
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 545
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 508
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 478
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 456
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 2
Columns("AG:AG").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=1
Range("AJ2").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=-1
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""STILL AVAILABLE"","""",RC[-4])"
Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""STILL AVAILABLE"",RC[-4],"""")"
Range("AK2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""STILL AVAILABLE"",RC[-6],””)"
Range("AJ2:AK2").Select
Range("AK2").Activate
Selection.AutoFill Destination:=Range("AJ2:AK10002"), Type:=xlFillDefault
Range("AJ2:AK10002").Select
ActiveWindow.ScrollRow = 9909
ActiveWindow.ScrollRow = 9886
ActiveWindow.ScrollRow = 9879
ActiveWindow.ScrollRow = 9864
ActiveWindow.ScrollRow = 9834
ActiveWindow.ScrollRow = 9797
ActiveWindow.ScrollRow = 9737
ActiveWindow.ScrollRow = 9670
ActiveWindow.ScrollRow = 9589
ActiveWindow.ScrollRow = 9477
ActiveWindow.ScrollRow = 9380
ActiveWindow.ScrollRow = 9283
ActiveWindow.ScrollRow = 9164
ActiveWindow.ScrollRow = 9038
ActiveWindow.ScrollRow = 8874
ActiveWindow.ScrollRow = 8740
ActiveWindow.ScrollRow = 8621
ActiveWindow.ScrollRow = 8502
ActiveWindow.ScrollRow = 8390
ActiveWindow.ScrollRow = 8234
ActiveWindow.ScrollRow = 8122
ActiveWindow.ScrollRow = 8003
ActiveWindow.ScrollRow = 7892
ActiveWindow.ScrollRow = 7750
ActiveWindow.ScrollRow = 7624
ActiveWindow.ScrollRow = 7460
ActiveWindow.ScrollRow = 7274
ActiveWindow.ScrollRow = 7125
ActiveWindow.ScrollRow = 6954
ActiveWindow.ScrollRow = 6745
ActiveWindow.ScrollRow = 6544
ActiveWindow.ScrollRow = 6366
ActiveWindow.ScrollRow = 6150
ActiveWindow.ScrollRow = 5956
ActiveWindow.ScrollRow = 5748
ActiveWindow.ScrollRow = 5532
ActiveWindow.ScrollRow = 5324
ActiveWindow.ScrollRow = 5100
ActiveWindow.ScrollRow = 4952
ActiveWindow.ScrollRow = 4788
ActiveWindow.ScrollRow = 4639
ActiveWindow.ScrollRow = 4512
ActiveWindow.ScrollRow = 4364
ActiveWindow.ScrollRow = 4207
ActiveWindow.ScrollRow = 4096
ActiveWindow.ScrollRow = 3991
ActiveWindow.ScrollRow = 3902
ActiveWindow.ScrollRow = 3798
ActiveWindow.ScrollRow = 3731
ActiveWindow.ScrollRow = 3642
ActiveWindow.ScrollRow = 3575
ActiveWindow.ScrollRow = 3508
ActiveWindow.ScrollRow = 3530
ActiveWindow.ScrollRow = 3589
ActiveWindow.ScrollRow = 3545
ActiveWindow.ScrollRow = 3448
ActiveWindow.ScrollRow = 3262
ActiveWindow.ScrollRow = 3009
ActiveWindow.ScrollRow = 2815
ActiveWindow.ScrollRow = 2533
ActiveWindow.ScrollRow = 2250
ActiveWindow.ScrollRow = 1997
ActiveWindow.ScrollRow = 1736
ActiveWindow.ScrollRow = 1520
ActiveWindow.ScrollRow = 1297
ActiveWindow.ScrollRow = 1104
ActiveWindow.ScrollRow = 970
ActiveWindow.ScrollRow = 865
ActiveWindow.ScrollRow = 769
ActiveWindow.ScrollRow = 709
ActiveWindow.ScrollRow = 657
ActiveWindow.ScrollRow = 612
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 560
ActiveWindow.ScrollRow = 553
ActiveWindow.ScrollRow = 545
ActiveWindow.ScrollRow = 530
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 523
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 508
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 478
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 449
ActiveWindow.ScrollRow = 441
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 2
Columns("AJ:AK").Select
Range("AK1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("AK:AK").Select
Selection.Replace What:="#NAME?", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 300
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 329
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 352
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 441
ActiveWindow.ScrollRow = 449
ActiveWindow.ScrollRow = 456
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 478
ActiveWindow.ScrollRow = 486
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 501
ActiveWindow.ScrollRow = 516
ActiveWindow.ScrollRow = 523
ActiveWindow.ScrollRow = 538
ActiveWindow.ScrollRow = 545
ActiveWindow.ScrollRow = 553
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 575
ActiveWindow.ScrollRow = 582
ActiveWindow.ScrollRow = 597
ActiveWindow.ScrollRow = 605
ActiveWindow.ScrollRow = 612
ActiveWindow.ScrollRow = 627
ActiveWindow.ScrollRow = 642
ActiveWindow.ScrollRow = 649
ActiveWindow.ScrollRow = 657
ActiveWindow.ScrollRow = 664
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 679
ActiveWindow.ScrollRow = 687
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 709
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 731
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 776
ActiveWindow.ScrollRow = 791
ActiveWindow.ScrollRow = 821
ActiveWindow.ScrollRow = 843
ActiveWindow.ScrollRow = 865
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 917
ActiveWindow.ScrollRow = 947
ActiveWindow.ScrollRow = 969
ActiveWindow.ScrollRow = 992
ActiveWindow.ScrollRow = 1029
ActiveWindow.ScrollRow = 1059
ActiveWindow.ScrollRow = 1081
ActiveWindow.ScrollRow = 1111
ActiveWindow.ScrollRow = 1141
ActiveWindow.ScrollRow = 1156
ActiveWindow.ScrollRow = 1178
ActiveWindow.ScrollRow = 1200
ActiveWindow.ScrollRow = 1215
ActiveWindow.ScrollRow = 1230
ActiveWindow.ScrollRow = 1237
ActiveWindow.ScrollRow = 1252
ActiveWindow.ScrollRow = 1275
ActiveWindow.ScrollRow = 1282
ActiveWindow.ScrollRow = 1304
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1342
ActiveWindow.ScrollRow = 1356
ActiveWindow.ScrollRow = 1371
ActiveWindow.ScrollRow = 1394
ActiveWindow.ScrollRow = 1416
ActiveWindow.ScrollRow = 1446
ActiveWindow.ScrollRow = 1468
ActiveWindow.ScrollRow = 1505
ActiveWindow.ScrollRow = 1535
ActiveWindow.ScrollRow = 1557
ActiveWindow.ScrollRow = 1580
ActiveWindow.ScrollRow = 1602
ActiveWindow.ScrollRow = 1624
ActiveWindow.ScrollRow = 1647
ActiveWindow.ScrollRow = 1669
ActiveWindow.ScrollRow = 1699
ActiveWindow.ScrollRow = 1721
ActiveWindow.ScrollRow = 1743
ActiveWindow.ScrollRow = 1766
ActiveWindow.ScrollRow = 1796
ActiveWindow.ScrollRow = 1825
ActiveWindow.ScrollRow = 1848
ActiveWindow.ScrollRow = 1870
ActiveWindow.ScrollRow = 1900
ActiveWindow.ScrollRow = 1930
ActiveWindow.ScrollRow = 1959
ActiveWindow.ScrollRow = 2004
ActiveWindow.ScrollRow = 2041
ActiveWindow.ScrollRow = 2078
ActiveWindow.ScrollRow = 2116
ActiveWindow.ScrollRow = 2168
ActiveWindow.ScrollRow = 2220
ActiveWindow.ScrollRow = 2264
ActiveWindow.ScrollRow = 2309
ActiveWindow.ScrollRow = 2376
ActiveWindow.ScrollRow = 2428
ActiveWindow.ScrollRow = 2480
ActiveWindow.ScrollRow = 2517
ActiveWindow.ScrollRow = 2570
ActiveWindow.ScrollRow = 2614
ActiveWindow.ScrollRow = 2666
ActiveWindow.ScrollRow = 2718
ActiveWindow.ScrollRow = 2778
ActiveWindow.ScrollRow = 2837
ActiveWindow.ScrollRow = 2897
ActiveWindow.ScrollRow = 2971
ActiveWindow.ScrollRow = 3024
ActiveWindow.ScrollRow = 3083
ActiveWindow.ScrollRow = 3150
ActiveWindow.ScrollRow = 3217
ActiveWindow.ScrollRow = 3277
ActiveWindow.ScrollRow = 3351
ActiveWindow.ScrollRow = 3411
ActiveWindow.ScrollRow = 3470
ActiveWindow.ScrollRow = 3544
ActiveWindow.ScrollRow = 3582
ActiveWindow.ScrollRow = 3619
ActiveWindow.ScrollRow = 3626
ActiveWindow.ScrollRow = 3634
ActiveWindow.ScrollRow = 3626
ActiveWindow.ScrollRow = 3619
ActiveWindow.ScrollRow = 3611
ActiveWindow.ScrollRow = 3604
ActiveWindow.ScrollRow = 3597
ActiveWindow.ScrollRow = 3589
ActiveWindow.ScrollRow = 3582
ActiveWindow.ScrollRow = 3574
ActiveWindow.ScrollRow = 3567
ActiveWindow.ScrollRow = 3559
ActiveWindow.ScrollRow = 3552
ActiveWindow.ScrollRow = 3544
ActiveWindow.ScrollRow = 3530
ActiveWindow.ScrollRow = 3507
ActiveWindow.ScrollRow = 3478
ActiveWindow.ScrollRow = 3396
ActiveWindow.ScrollRow = 3321
ActiveWindow.ScrollRow = 3202
ActiveWindow.ScrollRow = 3098
ActiveWindow.ScrollRow = 2979
ActiveWindow.ScrollRow = 2830
ActiveWindow.ScrollRow = 2644
ActiveWindow.ScrollRow = 2458
ActiveWindow.ScrollRow = 2250
ActiveWindow.ScrollRow = 2026
ActiveWindow.ScrollRow = 1803
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow = 1036
ActiveWindow.ScrollRow = 791
ActiveWindow.ScrollRow = 538
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 2
Columns("AJ:AK").Select
Range("AK1").Activate
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range( _
"AJ2:AJ10002"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("AJ1:AK10002")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("AJ2").Select
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
Range("AM2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<>"""",fuzzyvlookup(RC38,C36,1,,,R1C),"""")"
Range("AM2").Select
Selection.AutoFill Destination:=Range("AM2:AO2"), Type:=xlFillDefault
Range("AM2:AO2").Select
Range("AP2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]<>"""",fuzzypercent(RC38,RC[-3],R1C),"""")"
Range("AP2").Select
Selection.AutoFill Destination:=Range("AP2:AR2"), Type:=xlFillDefault
Range("AP2:AR2").Select
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
Range("AM2:AR2").Select
Selection.AutoFill Destination:=Range("AM2:AR100"), Type:=xlFillDefault
Range("AM2:AR100").Select
ActiveWindow.SmallScroll Down:=-57
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
Range("AS12").Select
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
Range("AW2").Select
Application.FormulaBarHeight = 6
ActiveCell.FormulaR1C1 = ""
Range("AW3").Select
Selection.AutoFill Destination:=Range("AW2:AW3"), Type:=xlFillDefault
Range("AW2:AW3").Select
Columns("BA:BF").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
Columns("D:AZ").Select
Range("AZ1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Selection.ClearContents
Range("D1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
End Sub

I unfortunately do not have the VBA training necessary to make a macro from scratch so I was stuck recording this macro. The problem I am having is that some of the data in columns BA to BF goes "missing" when the macro processes. This part is towards the end of the macro. If I am paste just the values of these dependent columns, why would this even occur? I have recorded plenty of macros and never seen this happen before. I tried re-recording the macro and got the same result. I am not sure if anyone can help on this and I don't know how to get the macro contents into a post properly. Here's a snippet from the end part with the pasting special of the content that is only being partially preserved. Thank You. Range("AW2").Select
Application.FormulaBarHeight = 6
ActiveCell.FormulaR1C1 = ""
Range("AW3").Select
Selection.AutoFill Destination:=Range("AW2:AW3"), Type:=xlFillDefault
Range("AW2:AW3").Select
Columns("BA:BF").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
Columns("D:AZ").Select
Range("AZ1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Selection.ClearContents
Range("D1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
End Sub
 

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.
Sort by date Sort by votes
A good method for troubleshooting code is to step through running it one line at a time (when viewing the code, hit F8 repeatedly). This will usually reveal the problem, or at least where in your code things are going wrong. Step through it and you will be able to identify the exact line that causes the problem.

Using the recorder's a good way to learn, but it does tend to include a lot of junk. All of the ActiveWindow.ScrollColumn references could be removed for example, the recorder was just capturing you scrolling around to get between your copy/pastes. It's generally good practice to clean up your code a bit after recording, as this will make modifications easier down the road. You also don't have to necessarily select cells to copy them.

Since you mentioned the problem looks like it happens at the end, I suspect it has something to do with your final clean up process. I'm not sure by your code if the missing data are from columns getting actually deleted (possible if you're deleting columns that include merged cells, accidentally expanding the range to unintended columns for example), or if formulas are just getting blanked out.
 
Upvote 0
Asala42 said:
A good method for troubleshooting code is to step through running it one line at a time (when viewing the code, hit F8 repeatedly). This will usually reveal the problem, or at least where in your code things are going wrong. Step through it and you will be able to identify the exact line that causes the problem.

Using the recorder's a good way to learn, but it does tend to include a lot of junk. All of the ActiveWindow.ScrollColumn references could be removed for example, the recorder was just capturing you scrolling around to get between your copy/pastes. It's generally good practice to clean up your code a bit after recording, as this will make modifications easier down the road. You also don't have to necessarily select cells to copy them.

Since you mentioned the problem looks like it happens at the end, I suspect it has something to do with your final clean up process. I'm not sure by your code if the missing data are from columns getting actually deleted (possible if you're deleting columns that include merged cells, accidentally expanding the range to unintended columns for example), or if formulas are just getting blanked out.
Click to expand...

Ok. I actually took care of the macro prior to reading your post. I wound up re-recording and not highlighting the columns or spreadsheet and pasting values at the end. I reran the macro and found that the fuzzylookup values underneath my initial fuzzyloop formulas (AM2:AO2) were showing up with error #VALUE for reasons unbeknownst to me. I simply autofilled the rows I needed info for 2:100 and the fuzzylookup errors were gone. So for my purposes, I am fine with just leaving it be. I might just create another macro just to do the autofill after the initial macro runs just so I save the user the trouble. Thanks for your advice. And just to clarify, I can just edit the code and get rid of any scrolling? Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
ActiveSheet. Paste error
Replies
1
Views
35
RoryA
RoryA
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
677
cstuder
C
P
  • Question
Help with Vba Code
Replies
3
Views
159
Coyotex3
C
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
311
VinceF
VinceF
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
188
ItalianPlatinum
I

Forum statistics

Threads
1,214,938
Messages
6,122,346
Members
449,080
Latest member
Armadillos

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