Streamlining a recorded macro

R

rc39452

Board Regular
Joined
Nov 9, 2004
Messages
77
Can someone please help me reduce the size of this macro. Any help is greatly appreciated.

I'm also trying to get the last part of it to work properly. The objective is to highlight the row that contain JE's (Located in column "b") that begins with "HQARM 3" "HQARM 4" "HQARM 8" "HQARM 9"......... :oops: :p


Sub MergeData()
'
' MergeData Macro
' by rl7799
'
Sheets("Data").Select
Range("B5").Select
Sheets("Data").Select
Cells.Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="ROAMER PROCESSING"
Range("D13").Select
ActiveCell.FormulaR1C1 = "INCOLLECT ROAMER COST"
Selection.FillDown
Selection.AutoFilter Field:=4
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],9)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D2948")
Range("D2:D2948").Select
Columns("D:D").ColumnWidth = 30
ActiveWindow.ScrollColumn = 3
Range("C1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-1],5)=""HQARM"",RC[-1],LEFT(RC[-1],3))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D2948")
Range("D2:D2948").Select
Range("C1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").ColumnWidth = 47.43
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-3],7)={""HQARM 3"",""HQARM 4"",""HQARM 8"",""HQARM 9""}),RC[-1],""NA"")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F2948")
Range("F2:F2948").Select
Range("E1").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 306
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 330
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 347
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 365
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 377
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 395
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 407
ActiveWindow.ScrollRow = 413
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 425
ActiveWindow.ScrollRow = 431
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 443
ActiveWindow.ScrollRow = 449
ActiveWindow.ScrollRow = 455
ActiveWindow.ScrollRow = 461
ActiveWindow.ScrollRow = 467
ActiveWindow.ScrollRow = 473
ActiveWindow.ScrollRow = 479
ActiveWindow.ScrollRow = 485
ActiveWindow.ScrollRow = 491
ActiveWindow.ScrollRow = 497
ActiveWindow.ScrollRow = 503
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 515
ActiveWindow.ScrollRow = 521
ActiveWindow.ScrollRow = 527
ActiveWindow.ScrollRow = 533
ActiveWindow.ScrollRow = 539
ActiveWindow.ScrollRow = 545
ActiveWindow.ScrollRow = 551
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 569
ActiveWindow.ScrollRow = 574
ActiveWindow.ScrollRow = 580
ActiveWindow.ScrollRow = 586
ActiveWindow.ScrollRow = 592
ActiveWindow.ScrollRow = 598
ActiveWindow.ScrollRow = 604
ActiveWindow.ScrollRow = 610
ActiveWindow.ScrollRow = 616
ActiveWindow.ScrollRow = 622
ActiveWindow.ScrollRow = 628
ActiveWindow.ScrollRow = 634
ActiveWindow.ScrollRow = 640
ActiveWindow.ScrollRow = 646
ActiveWindow.ScrollRow = 652
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 664
ActiveWindow.ScrollRow = 670
ActiveWindow.ScrollRow = 676
ActiveWindow.ScrollRow = 682
ActiveWindow.ScrollRow = 688
ActiveWindow.ScrollRow = 694
ActiveWindow.ScrollRow = 700
ActiveWindow.ScrollRow = 706
ActiveWindow.ScrollRow = 712
ActiveWindow.ScrollRow = 718
ActiveWindow.ScrollRow = 724
ActiveWindow.ScrollRow = 730
ActiveWindow.ScrollRow = 736
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 754
ActiveWindow.ScrollRow = 766
ActiveWindow.ScrollRow = 772
ActiveWindow.ScrollRow = 778
ActiveWindow.ScrollRow = 784
ActiveWindow.ScrollRow = 790
ActiveWindow.ScrollRow = 796
ActiveWindow.ScrollRow = 801
ActiveWindow.ScrollRow = 807
ActiveWindow.ScrollRow = 813
ActiveWindow.ScrollRow = 819
ActiveWindow.ScrollRow = 825
ActiveWindow.ScrollRow = 831
ActiveWindow.ScrollRow = 837
ActiveWindow.ScrollRow = 849
ActiveWindow.ScrollRow = 861
ActiveWindow.ScrollRow = 873
ActiveWindow.ScrollRow = 879
ActiveWindow.ScrollRow = 885
ActiveWindow.ScrollRow = 891
ActiveWindow.ScrollRow = 897
ActiveWindow.ScrollRow = 903
ActiveWindow.ScrollRow = 909
ActiveWindow.ScrollRow = 915
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 927
ActiveWindow.ScrollRow = 939
ActiveWindow.ScrollRow = 951
ActiveWindow.ScrollRow = 957
ActiveWindow.ScrollRow = 963
ActiveWindow.ScrollRow = 969
ActiveWindow.ScrollRow = 975
ActiveWindow.ScrollRow = 981
ActiveWindow.ScrollRow = 987
ActiveWindow.ScrollRow = 993
ActiveWindow.ScrollRow = 999
ActiveWindow.ScrollRow = 1005
ActiveWindow.ScrollRow = 1011
ActiveWindow.ScrollRow = 1017
ActiveWindow.ScrollRow = 1028
ActiveWindow.ScrollRow = 1034
ActiveWindow.ScrollRow = 1040
ActiveWindow.ScrollRow = 1046
ActiveWindow.ScrollRow = 1052
ActiveWindow.ScrollRow = 1058
ActiveWindow.ScrollRow = 1064
ActiveWindow.ScrollRow = 1070
ActiveWindow.ScrollRow = 1076
ActiveWindow.ScrollRow = 1082
ActiveWindow.ScrollRow = 1088
ActiveWindow.ScrollRow = 1094
ActiveWindow.ScrollRow = 1100
ActiveWindow.ScrollRow = 1106
ActiveWindow.ScrollRow = 1112
ActiveWindow.ScrollRow = 1124
ActiveWindow.ScrollRow = 1130
ActiveWindow.ScrollRow = 1136
ActiveWindow.ScrollRow = 1142
ActiveWindow.ScrollRow = 1148
ActiveWindow.ScrollRow = 1154
ActiveWindow.ScrollRow = 1160
ActiveWindow.ScrollRow = 1166
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1178
ActiveWindow.ScrollRow = 1184
ActiveWindow.ScrollRow = 1196
ActiveWindow.ScrollRow = 1208
ActiveWindow.ScrollRow = 1214
ActiveWindow.ScrollRow = 1220
ActiveWindow.ScrollRow = 1226
ActiveWindow.ScrollRow = 1232
ActiveWindow.ScrollRow = 1238
ActiveWindow.ScrollRow = 1244
ActiveWindow.ScrollRow = 1250
ActiveWindow.ScrollRow = 1255
ActiveWindow.ScrollRow = 1267
ActiveWindow.ScrollRow = 1285
ActiveWindow.ScrollRow = 1303
ActiveWindow.ScrollRow = 1309
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1339
ActiveWindow.ScrollRow = 1357
ActiveWindow.ScrollRow = 1369
ActiveWindow.ScrollRow = 1381
ActiveWindow.ScrollRow = 1405
ActiveWindow.ScrollRow = 1423
ActiveWindow.ScrollRow = 1441
ActiveWindow.ScrollRow = 1465
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 1500
ActiveWindow.ScrollRow = 1518
ActiveWindow.ScrollRow = 1536
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1578
ActiveWindow.ScrollRow = 1602
ActiveWindow.ScrollRow = 1626
ActiveWindow.ScrollRow = 1638
ActiveWindow.ScrollRow = 1662
ActiveWindow.ScrollRow = 1680
ActiveWindow.ScrollRow = 1698
ActiveWindow.ScrollRow = 1710
ActiveWindow.ScrollRow = 1727
ActiveWindow.ScrollRow = 1745
ActiveWindow.ScrollRow = 1769
ActiveWindow.ScrollRow = 1781
ActiveWindow.ScrollRow = 1805
ActiveWindow.ScrollRow = 1817
ActiveWindow.ScrollRow = 1835
ActiveWindow.ScrollRow = 1859
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1895
ActiveWindow.ScrollRow = 1913
ActiveWindow.ScrollRow = 1937
ActiveWindow.ScrollRow = 1954
ActiveWindow.ScrollRow = 1972
ActiveWindow.ScrollRow = 1990
ActiveWindow.ScrollRow = 2014
ActiveWindow.ScrollRow = 2026
ActiveWindow.ScrollRow = 2050
ActiveWindow.ScrollRow = 2062
ActiveWindow.ScrollRow = 2080
ActiveWindow.ScrollRow = 2092
ActiveWindow.ScrollRow = 2104
ActiveWindow.ScrollRow = 2122
ActiveWindow.ScrollRow = 2128
ActiveWindow.ScrollRow = 2146
ActiveWindow.ScrollRow = 2164
ActiveWindow.ScrollRow = 2169
ActiveWindow.ScrollRow = 2187
ActiveWindow.ScrollRow = 2199
ActiveWindow.ScrollRow = 2211
ActiveWindow.ScrollRow = 2217
ActiveWindow.ScrollRow = 2229
ActiveWindow.ScrollRow = 2247
ActiveWindow.ScrollRow = 2253
ActiveWindow.ScrollRow = 2265
ActiveWindow.ScrollRow = 2283
ActiveWindow.ScrollRow = 2289
ActiveWindow.ScrollRow = 2301
ActiveWindow.ScrollRow = 2319
ActiveWindow.ScrollRow = 2325
ActiveWindow.ScrollRow = 2343
ActiveWindow.ScrollRow = 2349
ActiveWindow.ScrollRow = 2367
ActiveWindow.ScrollRow = 2385
ActiveWindow.ScrollRow = 2391
ActiveWindow.ScrollRow = 2408
ActiveWindow.ScrollRow = 2426
ActiveWindow.ScrollRow = 2432
ActiveWindow.ScrollRow = 2450
ActiveWindow.ScrollRow = 2462
ActiveWindow.ScrollRow = 2468
ActiveWindow.ScrollRow = 2480
ActiveWindow.ScrollRow = 2486
ActiveWindow.ScrollRow = 2504
ActiveWindow.ScrollRow = 2516
ActiveWindow.ScrollRow = 2534
ActiveWindow.ScrollRow = 2546
ActiveWindow.ScrollRow = 2570
ActiveWindow.ScrollRow = 2582
ActiveWindow.ScrollRow = 2600
ActiveWindow.ScrollRow = 2623
ActiveWindow.ScrollRow = 2635
ActiveWindow.ScrollRow = 2659
ActiveWindow.ScrollRow = 2671
ActiveWindow.ScrollRow = 2695
ActiveWindow.ScrollRow = 2719
ActiveWindow.ScrollRow = 2743
ActiveWindow.ScrollRow = 2761
ActiveWindow.ScrollRow = 2773
ActiveWindow.ScrollRow = 2797
ActiveWindow.ScrollRow = 2815
ActiveWindow.ScrollRow = 2833
ActiveWindow.ScrollRow = 2839
ActiveWindow.ScrollRow = 2856
ActiveWindow.ScrollRow = 2868
ActiveWindow.ScrollRow = 2880
ActiveWindow.ScrollRow = 2886
ActiveWindow.ScrollRow = 2892
ActiveWindow.ScrollRow = 2886
ActiveWindow.ScrollRow = 2880
ActiveWindow.ScrollRow = 2874
ActiveWindow.ScrollRow = 2868
ActiveWindow.ScrollRow = 2862
ActiveWindow.ScrollRow = 2856
ActiveWindow.ScrollRow = 2850
ActiveWindow.ScrollRow = 2845
ActiveWindow.ScrollRow = 2839
ActiveWindow.ScrollRow = 2833
ActiveWindow.ScrollRow = 2827
ActiveWindow.ScrollRow = 2821
ActiveWindow.ScrollRow = 2815
ActiveWindow.ScrollRow = 2809
ActiveWindow.ScrollRow = 2797
ActiveWindow.ScrollRow = 2791
ActiveWindow.ScrollRow = 2785
ActiveWindow.ScrollRow = 2779
ActiveWindow.ScrollRow = 2773
ActiveWindow.ScrollRow = 2767
ActiveWindow.ScrollRow = 2761
ActiveWindow.ScrollRow = 2755
ActiveWindow.ScrollRow = 2761
ActiveWindow.ScrollRow = 2767
ActiveWindow.ScrollRow = 2773
ActiveWindow.ScrollRow = 2779
ActiveWindow.ScrollRow = 2785
ActiveWindow.ScrollRow = 2791
ActiveWindow.ScrollRow = 2797
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 2791
ActiveWindow.ScrollRow = 2785
ActiveWindow.ScrollRow = 2779
ActiveWindow.ScrollRow = 2767
ActiveWindow.ScrollRow = 2755
ActiveWindow.ScrollRow = 2743
ActiveWindow.ScrollRow = 2725
ActiveWindow.ScrollRow = 2707
ActiveWindow.ScrollRow = 2671
ActiveWindow.ScrollRow = 2653
ActiveWindow.ScrollRow = 2612
ActiveWindow.ScrollRow = 2576
ActiveWindow.ScrollRow = 2528
ActiveWindow.ScrollRow = 2486
ActiveWindow.ScrollRow = 2432
ActiveWindow.ScrollRow = 2373
ActiveWindow.ScrollRow = 2325
ActiveWindow.ScrollRow = 2253
ActiveWindow.ScrollRow = 2181
ActiveWindow.ScrollRow = 2122
ActiveWindow.ScrollRow = 2044
ActiveWindow.ScrollRow = 1966
ActiveWindow.ScrollRow = 1883
ActiveWindow.ScrollRow = 1805
ActiveWindow.ScrollRow = 1727
ActiveWindow.ScrollRow = 1644
ActiveWindow.ScrollRow = 1566
ActiveWindow.ScrollRow = 1488
ActiveWindow.ScrollRow = 1405
ActiveWindow.ScrollRow = 1339
ActiveWindow.ScrollRow = 1267
ActiveWindow.ScrollRow = 1196
ActiveWindow.ScrollRow = 1136
ActiveWindow.ScrollRow = 1076
ActiveWindow.ScrollRow = 1023
ActiveWindow.ScrollRow = 981
ActiveWindow.ScrollRow = 933
ActiveWindow.ScrollRow = 891
ActiveWindow.ScrollRow = 837
ActiveWindow.ScrollRow = 807
ActiveWindow.ScrollRow = 778
ActiveWindow.ScrollRow = 754
ActiveWindow.ScrollRow = 730
ActiveWindow.ScrollRow = 706
ActiveWindow.ScrollRow = 682
ActiveWindow.ScrollRow = 652
ActiveWindow.ScrollRow = 616
ActiveWindow.ScrollRow = 598
ActiveWindow.ScrollRow = 574
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 539
ActiveWindow.ScrollRow = 533
ActiveWindow.ScrollRow = 521
ActiveWindow.ScrollRow = 503
ActiveWindow.ScrollRow = 491
ActiveWindow.ScrollRow = 479
ActiveWindow.ScrollRow = 461
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 425
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 365
ActiveWindow.ScrollRow = 347
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 300
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 1
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R2948C6").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Line Item")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Batch Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Line Description")
.Orientation = xlRowField
.Position = 3
End With
Range("B4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Batch Name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Period")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Net"), "Sum of Net", xlSum
ActiveWindow.ScrollColumn = 2
Range("D5:G100").Select
Selection.Style = "Comma"
Columns("D:G").Select
Columns("D:G").EntireColumn.AutoFit
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
Range("E74").Select
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1

Range("A1").Select
ActiveCell.FormulaR1C1 = "Cingular Wireless"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Partnership Journal Entries"
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
ActiveCell.FormulaR1C1 = "Company Code:______________"
Range("A1:A3").Select
Selection.Font.Bold = True
Rows("4:4").Select
Selection.Insert Shift:=xlDown

Range("D7:Q104").Select
Selection.Style = "Comma"
Columns("D:S").Select
Selection.ColumnWidth = 15

'If cell starts with "HQARM 3" or "HQARM 4" or "HQARM 8" or "HQARM 9" then turn entire row background yellow
If Left("B:B", 7) = "HQARM 4" Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End If


End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Two quick things to shorten it:

1. Get rid of all the "ActiveWindow.ScrollRow =" lines. This simply recorded all your scrolling while recording the macro and is not necessary.

2. Many of the Select statements can be combined with the following line if Selection is the beginning of the next line.
i.e.
Range("E1").Select
Selection.Copy
can be combined to
Range("E1").Copy
 
Upvote 0
For a start you can remove all the ActiveWindow.ScrollRow statements.

Code: 
Sub MergeData()
'
' MergeData Macro
' by rl7799
'
Sheets("Data").Select
Range("B5").Select
Sheets("Data").Select
Cells.Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="ROAMER PROCESSING"
Range("D13").Select
ActiveCell.FormulaR1C1 = "INCOLLECT ROAMER COST"
Selection.FillDown
Selection.AutoFilter Field:=4
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],9)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D2948")
Range("D2:D2948").Select
Columns("D:D").ColumnWidth = 30
ActiveWindow.ScrollColumn = 3
Range("C1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-1],5)=""HQARM"",RC[-1],LEFT(RC[-1],3))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D2948")
Range("D2:D2948").Select
Range("C1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").ColumnWidth = 47.43
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-3],7)={""HQARM 3"",""HQARM 4"",""HQARM 8"",""HQARM 9""}),RC[-1],""NA"")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F2948")
Range("F2:F2948").Select
Range("E1").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R2948C6").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Line Item")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Batch Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Line Description")
.Orientation = xlRowField
.Position = 3
End With
Range("B4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Batch Name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Period")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Net"), "Sum of Net", xlSum

Range("D5:G100").Select
Selection.Style = "Comma"
Columns("D:G").Select
Columns("D:G").EntireColumn.AutoFit

Range("A1").Select
ActiveCell.FormulaR1C1 = "Cingular Wireless"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Partnership Journal Entries"
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
ActiveCell.FormulaR1C1 = "Company Code:______________"
Range("A1:A3").Select
Selection.Font.Bold = True
Rows("4:4").Select
Selection.Insert Shift:=xlDown

Range("D7:Q104").Select
Selection.Style = "Comma"
Columns("D:S").Select
Selection.ColumnWidth = 15

'If cell starts with "HQARM 3" or "HQARM 4" or "HQARM 8" or "HQARM 9" then turn entire row background yellow
If Left("B:B", 7) = "HQARM 4" Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End If


End Sub

BTW that If statement at the end is not going to work as far as I can see.
 
Upvote 0
First, you don't have to necessarily use Select statement. Most of the times when there is a Select statement and an operation is done in next line, you can delete that select and bring the line below up. Read carefully what select is doing though.

And if you want to make the row yellow you can use conditional formatting.
 
Upvote 0
The first thing that you can do is get rid of the Select statements:

<font face=Tahoma>Sheets("Data").Select
Range("B5").Select
Sheets("Data").Select
Cells.Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
</FONT>Can be:

<font face=Tahoma>Sheets("Data").Cells.Copy Sheets("Sheet1").Range("A1")</FONT>Almost anytime you see "Select" followed by "Selection", you can eliminate both statements.

You also only need Application.CutCopyMode 1x (at the end).

Putting Application.ScreenUpdating = False at the beginning of your code will help too, just set it to False at the end.

Also lose all of the ActiveWindow.Scroll code lines.

For the last part, try:

<font face=Tahoma>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#007F00">'If cell starts with "HQARM 3" or "HQARM 4" or "HQARM 8" or "HQARM 9" then turn entire row background yellow</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([B1], [B65536].End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> Left(c, 7) = "HQARM 3" <SPAN style="color:#00007F">Or</SPAN> Left(c, 7) = "HQARM 4" <SPAN style="color:#00007F">Or</SPAN> Left(c, 7) = "HQARM 8" <SPAN style="color:#00007F">Or</SPAN> Left(c, 7) = "HQARM 9" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">With</SPAN> c.EntireRow.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 
Upvote 0
For the last part can't you use Conditional Formatting?

That can be done using code as well.
 
Upvote 0
Or try:

Sub Macro1()
'
Dim c As Object
For Each c In Selection
Cells.Find(What:="HQARM", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
With ActiveCell.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Next c
End Sub
 
Upvote 0
thanks to all.......!!! Norie,A7N9, Jmiskey you guys are truly experts!

Pennysaver the bottom portion works like a charm!

This is the best site ever!

(y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
142
Coyotex3
C
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
296
VinceF
VinceF
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
662
cstuder
C
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
160
ItalianPlatinum
I
D
  • Question
Macro to Keep Sheet Names as Sheet1 and Sheet2
2
Replies
14
Views
831
djmnon
D

Forum statistics

Threads
1,214,415
Messages
6,119,377
Members
448,888
Latest member
Arle8907

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