macro that changes worksheets not a specific one?

lablover1

New Member
Joined
Mar 21, 2013
Messages
41
I have tried Sheets("").Select to just have the macro work on any worksheet in a workbook not just on the specific worksheet (i.e.Sheets("4474-60-2").Select . Get an error though. Please advise.:confused: Thank you
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
What do yo mean work on any sheet?
Are you trying to run it against all sheets?
If not, how does it know which sheet you want the code to run against?
Is it just the active worksheet? If so, you may not not need any worksheet reference at all.
 
Upvote 0
Then why do you need a specific worksheet reference at all?
Is it just going to run on the current/active sheet?
If not, how are you determining which sheet to run it against, at any point in time (I am asking for the logic behind how it should work)?
 
Upvote 0
I think it would be better to have the macro work on one active worksheet (sometimes, the various worksheets are of different lengths)
 
Upvote 0
Consider using the "For Each" construct:
That code provided will run on ALL sheets, which I thought you did NOT want.
I think it would be better to have the macro work on one active worksheet (sometimes, the various worksheets are of different lengths)
If you just want it to run on the active worksheet, then you may not need any worksheet reference at all, as the default is to run on the active worksheet, if the worksheet is not referenced. So unless within your code you are involving multiple worksheets, you probably do not need any worksheet reference.

Perhaps it would be best if we could see exactly what you are doing, if you could post your code and explain what you are trying to do.
 
Upvote 0
I am trying to remove text from the columns that shows number data.
Code:
Code:
 Sub Macro1()
'
' Macro1 Macro
'
'
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"<", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
OtherChar:="<", FieldInfo:=Array(Array(0, 1), Array(1, 9), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("C:C").Select
Range("E2").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.NumberFormat = "0.00"
Range("G2").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.NumberFormat = "0.00"
Range("I2").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("I:I").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 311
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 338
ActiveWindow.ScrollRow = 346
ActiveWindow.ScrollRow = 352
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 362
ActiveWindow.ScrollRow = 370
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 409
ActiveWindow.ScrollRow = 421
ActiveWindow.ScrollRow = 430
ActiveWindow.ScrollRow = 442
ActiveWindow.ScrollRow = 456
ActiveWindow.ScrollRow = 470
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 497
ActiveWindow.ScrollRow = 511
ActiveWindow.ScrollRow = 532
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 589
ActiveWindow.ScrollRow = 611
ActiveWindow.ScrollRow = 630
ActiveWindow.ScrollRow = 652
ActiveWindow.ScrollRow = 674
ActiveWindow.ScrollRow = 697
ActiveWindow.ScrollRow = 721
ActiveWindow.ScrollRow = 744
ActiveWindow.ScrollRow = 766
ActiveWindow.ScrollRow = 789
ActiveWindow.ScrollRow = 809
ActiveWindow.ScrollRow = 825
ActiveWindow.ScrollRow = 840
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 868
ActiveWindow.ScrollRow = 876
ActiveWindow.ScrollRow = 885
ActiveWindow.ScrollRow = 893
ActiveWindow.ScrollRow = 897
ActiveWindow.ScrollRow = 903
ActiveWindow.ScrollRow = 907
ActiveWindow.ScrollRow = 909
ActiveWindow.ScrollRow = 911
ActiveWindow.ScrollRow = 913
ActiveWindow.ScrollRow = 917
ActiveWindow.ScrollRow = 919
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 925
ActiveWindow.ScrollRow = 932
ActiveWindow.ScrollRow = 936
ActiveWindow.ScrollRow = 944
ActiveWindow.ScrollRow = 956
ActiveWindow.ScrollRow = 970
ActiveWindow.ScrollRow = 985
ActiveWindow.ScrollRow = 1001
ActiveWindow.ScrollRow = 1017
ActiveWindow.ScrollRow = 1038
ActiveWindow.ScrollRow = 1062
ActiveWindow.ScrollRow = 1087
ActiveWindow.ScrollRow = 1111
ActiveWindow.ScrollRow = 1138
ActiveWindow.ScrollRow = 1162
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1209
ActiveWindow.ScrollRow = 1234
ActiveWindow.ScrollRow = 1254
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1299
ActiveWindow.ScrollRow = 1307
ActiveWindow.ScrollRow = 1313
ActiveWindow.ScrollRow = 1315
ActiveWindow.ScrollRow = 1317
ActiveWindow.ScrollRow = 1319
ActiveWindow.ScrollRow = 1323
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1332
ActiveWindow.ScrollRow = 1342
ActiveWindow.ScrollRow = 1354
ActiveWindow.ScrollRow = 1368
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1405
ActiveWindow.ScrollRow = 1421
ActiveWindow.ScrollRow = 1442
ActiveWindow.ScrollRow = 1464
ActiveWindow.ScrollRow = 1483
ActiveWindow.ScrollRow = 1503
ActiveWindow.ScrollRow = 1519
ActiveWindow.ScrollRow = 1536
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1572
ActiveWindow.ScrollRow = 1587
ActiveWindow.ScrollRow = 1601
ActiveWindow.ScrollRow = 1615
ActiveWindow.ScrollRow = 1627
ActiveWindow.ScrollRow = 1638
ActiveWindow.ScrollRow = 1644
ActiveWindow.ScrollRow = 1652
ActiveWindow.ScrollRow = 1656
ActiveWindow.ScrollRow = 1650
ActiveWindow.ScrollRow = 1646
ActiveWindow.ScrollRow = 1640
ActiveWindow.ScrollRow = 1636
ActiveWindow.ScrollRow = 1632
ActiveWindow.ScrollRow = 1625
ActiveWindow.ScrollRow = 1621
ActiveWindow.ScrollRow = 1619
ActiveWindow.ScrollRow = 1617
ActiveWindow.ScrollRow = 1613
ActiveWindow.ScrollRow = 1611
ActiveWindow.ScrollRow = 1609
ActiveWindow.ScrollRow = 1607
ActiveWindow.ScrollRow = 1605
ActiveWindow.ScrollRow = 1603
ActiveWindow.ScrollRow = 1601
ActiveWindow.ScrollRow = 1597
ActiveWindow.ScrollRow = 1593
ActiveWindow.ScrollRow = 1589
ActiveWindow.ScrollRow = 1583
ActiveWindow.ScrollRow = 1578
ActiveWindow.ScrollRow = 1574
ActiveWindow.ScrollRow = 1570
ActiveWindow.ScrollRow = 1568
ActiveWindow.ScrollRow = 1564
ActiveWindow.ScrollRow = 1562
ActiveWindow.ScrollRow = 1560
ActiveWindow.ScrollRow = 1558
ActiveWindow.ScrollRow = 1556
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1552
ActiveWindow.ScrollRow = 1550
ActiveWindow.ScrollRow = 1548
ActiveWindow.ScrollRow = 1546
ActiveWindow.ScrollRow = 1544
ActiveWindow.ScrollRow = 1542
ActiveWindow.ScrollRow = 1540
ActiveWindow.ScrollRow = 1544
ActiveWindow.ScrollRow = 1546
ActiveWindow.ScrollRow = 1550
ActiveWindow.ScrollRow = 1556
ActiveWindow.ScrollRow = 1562
ActiveWindow.ScrollRow = 1570
ActiveWindow.ScrollRow = 1580
ActiveWindow.ScrollRow = 1585
ActiveWindow.ScrollRow = 1591
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1601
ActiveWindow.ScrollRow = 1605
ActiveWindow.ScrollRow = 1611
ActiveWindow.ScrollRow = 1615
ActiveWindow.ScrollRow = 1617
ActiveWindow.ScrollRow = 1619
ActiveWindow.ScrollRow = 1623
ActiveWindow.ScrollRow = 1627
ActiveWindow.ScrollRow = 1629
ActiveWindow.ScrollRow = 1632
ActiveWindow.ScrollRow = 1627
ActiveWindow.ScrollRow = 1625
ActiveWindow.ScrollRow = 1621
ActiveWindow.ScrollRow = 1615
ActiveWindow.ScrollRow = 1609
ActiveWindow.ScrollRow = 1601
ActiveWindow.ScrollRow = 1591
ActiveWindow.ScrollRow = 1583
ActiveWindow.ScrollRow = 1574
ActiveWindow.ScrollRow = 1566
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1546
ActiveWindow.ScrollRow = 1534
ActiveWindow.ScrollRow = 1527
ActiveWindow.ScrollRow = 1519
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1505
ActiveWindow.ScrollRow = 1497
ActiveWindow.ScrollRow = 1491
ActiveWindow.ScrollRow = 1485
ActiveWindow.ScrollRow = 1478
ActiveWindow.ScrollRow = 1472
ActiveWindow.ScrollRow = 1464
ActiveWindow.ScrollRow = 1454
ActiveWindow.ScrollRow = 1446
ActiveWindow.ScrollRow = 1440
ActiveWindow.ScrollRow = 1436
ActiveWindow.ScrollRow = 1432
ActiveWindow.ScrollRow = 1427
ActiveWindow.ScrollRow = 1423
ActiveWindow.ScrollRow = 1419
ActiveWindow.ScrollRow = 1415
ActiveWindow.ScrollRow = 1411
ActiveWindow.ScrollRow = 1405
ActiveWindow.ScrollRow = 1397
ActiveWindow.ScrollRow = 1389
ActiveWindow.ScrollRow = 1381
ActiveWindow.ScrollRow = 1360
ActiveWindow.ScrollRow = 1344
ActiveWindow.ScrollRow = 1323
ActiveWindow.ScrollRow = 1299
ActiveWindow.ScrollRow = 1274
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1168
ActiveWindow.ScrollRow = 1138
ActiveWindow.ScrollRow = 1105
ActiveWindow.ScrollRow = 1062
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 954
ActiveWindow.ScrollRow = 846
ActiveWindow.ScrollRow = 795
ActiveWindow.ScrollRow = 738
ActiveWindow.ScrollRow = 681
ActiveWindow.ScrollRow = 630
ActiveWindow.ScrollRow = 572
ActiveWindow.ScrollRow = 521
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 407
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 1
End Sub
Sub Macro4()
'
' Macro4 Macro
'
'
Range("E3").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.NumberFormat = "0.00"
Range("G3").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("F3").Select
Selection.TextToColumns Destination:=Range("F3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.NumberFormat = "0.00"
Range("H3").Select
Range("I3").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("I:I").Select
Selection.NumberFormat = "0.00"
Range("K3").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
"=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 298
ActiveWindow.ScrollRow = 311
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 339
ActiveWindow.ScrollRow = 349
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 408
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 414
ActiveWindow.ScrollRow = 417
ActiveWindow.ScrollRow = 421
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 430
ActiveWindow.ScrollRow = 433
ActiveWindow.ScrollRow = 436
ActiveWindow.ScrollRow = 439
ActiveWindow.ScrollRow = 446
ActiveWindow.ScrollRow = 452
ActiveWindow.ScrollRow = 455
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 471
ActiveWindow.ScrollRow = 486
ActiveWindow.ScrollRow = 505
ActiveWindow.ScrollRow = 527
ActiveWindow.ScrollRow = 549
ActiveWindow.ScrollRow = 571
ActiveWindow.ScrollRow = 593
ActiveWindow.ScrollRow = 618
ActiveWindow.ScrollRow = 643
ActiveWindow.ScrollRow = 665
ActiveWindow.ScrollRow = 690
ActiveWindow.ScrollRow = 712
ActiveWindow.ScrollRow = 734
ActiveWindow.ScrollRow = 756
ActiveWindow.ScrollRow = 778
ActiveWindow.ScrollRow = 800
ActiveWindow.ScrollRow = 815
ActiveWindow.ScrollRow = 837
ActiveWindow.ScrollRow = 859
ActiveWindow.ScrollRow = 878
ActiveWindow.ScrollRow = 900
ActiveWindow.ScrollRow = 922
ActiveWindow.ScrollRow = 937
ActiveWindow.ScrollRow = 959
ActiveWindow.ScrollRow = 978
ActiveWindow.ScrollRow = 994
ActiveWindow.ScrollRow = 1012
ActiveWindow.ScrollRow = 1025
ActiveWindow.ScrollRow = 1041
ActiveWindow.ScrollRow = 1059
ActiveWindow.ScrollRow = 1078
ActiveWindow.ScrollRow = 1094
ActiveWindow.ScrollRow = 1113
ActiveWindow.ScrollRow = 1128
ActiveWindow.ScrollRow = 1150
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1194
ActiveWindow.ScrollRow = 1216
ActiveWindow.ScrollRow = 1244
ActiveWindow.ScrollRow = 1269
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow = 1332
ActiveWindow.ScrollRow = 1357
ActiveWindow.ScrollRow = 1382
ActiveWindow.ScrollRow = 1407
ActiveWindow.ScrollRow = 1432
ActiveWindow.ScrollRow = 1454
ActiveWindow.ScrollRow = 1476
ActiveWindow.ScrollRow = 1488
ActiveWindow.ScrollRow = 1504
ActiveWindow.ScrollRow = 1510
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1517
ActiveWindow.ScrollRow = 1520
ActiveWindow.ScrollRow = 1523
ActiveWindow.ScrollRow = 1526
ActiveWindow.ScrollRow = 1535
ActiveWindow.ScrollRow = 1542
ActiveWindow.ScrollRow = 1551
ActiveWindow.ScrollRow = 1570
ActiveWindow.ScrollRow = 1592
ActiveWindow.ScrollRow = 1620
ActiveWindow.ScrollRow = 1642
ActiveWindow.ScrollRow = 1670
ActiveWindow.ScrollRow = 1705
ActiveWindow.ScrollRow = 1736
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1805
ActiveWindow.ScrollRow = 1833
ActiveWindow.ScrollRow = 1874
ActiveWindow.ScrollRow = 1899
ActiveWindow.ScrollRow = 1927
ActiveWindow.ScrollRow = 1955
ActiveWindow.ScrollRow = 1983
ActiveWindow.ScrollRow = 2011
ActiveWindow.ScrollRow = 2040
ActiveWindow.ScrollRow = 2068
ActiveWindow.ScrollRow = 2096
ActiveWindow.ScrollRow = 2118
ActiveWindow.ScrollRow = 2137
ActiveWindow.ScrollRow = 2159
ActiveWindow.ScrollRow = 2180
ActiveWindow.ScrollRow = 2193
ActiveWindow.ScrollRow = 2206
ActiveWindow.ScrollRow = 2215
ActiveWindow.ScrollRow = 2221
ActiveWindow.ScrollRow = 2224
ActiveWindow.ScrollRow = 2227
ActiveWindow.ScrollRow = 2234
ActiveWindow.ScrollRow = 2243
ActiveWindow.ScrollRow = 2256
ActiveWindow.ScrollRow = 2278
ActiveWindow.ScrollRow = 2306
ActiveWindow.ScrollRow = 2331
ActiveWindow.ScrollRow = 2362
ActiveWindow.ScrollRow = 2397
ActiveWindow.ScrollRow = 2431
ActiveWindow.ScrollRow = 2469
ActiveWindow.ScrollRow = 2516
ActiveWindow.ScrollRow = 2550
ActiveWindow.ScrollRow = 2519
ActiveWindow.ScrollRow = 2453
ActiveWindow.ScrollRow = 2372
ActiveWindow.ScrollRow = 2293
ActiveWindow.ScrollRow = 2202
ActiveWindow.ScrollRow = 2115
ActiveWindow.ScrollRow = 2036
ActiveWindow.ScrollRow = 1949
ActiveWindow.ScrollRow = 1870
ActiveWindow.ScrollRow = 1792
ActiveWindow.ScrollRow = 1730
ActiveWindow.ScrollRow = 1673
ActiveWindow.ScrollRow = 1636
ActiveWindow.ScrollRow = 1589
ActiveWindow.ScrollRow = 1548
ActiveWindow.ScrollRow = 1504
ActiveWindow.ScrollRow = 1470
ActiveWindow.ScrollRow = 1432
ActiveWindow.ScrollRow = 1388
ActiveWindow.ScrollRow = 1354
ActiveWindow.ScrollRow = 1316
ActiveWindow.ScrollRow = 1282
ActiveWindow.ScrollRow = 1244
ActiveWindow.ScrollRow = 1210
ActiveWindow.ScrollRow = 1175
ActiveWindow.ScrollRow = 1144
ActiveWindow.ScrollRow = 1110
ActiveWindow.ScrollRow = 1075
ActiveWindow.ScrollRow = 1038
ActiveWindow.ScrollRow = 1003
ActiveWindow.ScrollRow = 965
ActiveWindow.ScrollRow = 931
ActiveWindow.ScrollRow = 897
ActiveWindow.ScrollRow = 853
ActiveWindow.ScrollRow = 815
ActiveWindow.ScrollRow = 768
ActiveWindow.ScrollRow = 734
ActiveWindow.ScrollRow = 693
ActiveWindow.ScrollRow = 640
ActiveWindow.ScrollRow = 599
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 505
ActiveWindow.ScrollRow = 446
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 1
End Sub
[/end code]
 
Upvote 0
I see you are using the Macro Recorder. You can clean-up a lot of your code. You can get rid of all your "ActiveWindow.ScrollRow" rows. All that is is recorded steps of you scrolling down the page. It adds nothing of value to your code. Also, just about any rows that end in "Select" where the next line begins with "Selection" can be combined into one row.

So, you code for your second macro could be summarized like this:
Code:
Sub Macro4()
'
' Macro4 Macro
'
'
    Range("E3").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
        "=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("E:E").NumberFormat = "0.00"

    Range("G3").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
        "=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("G:G").NumberFormat = "0.00"
    
    Range("I3").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
        "=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("I:I").NumberFormat = "0.00"
    
    Range("K3").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=True, Other:=True, OtherChar:= _
        "=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

End Sub
Nowhere in your code are any worksheet references, meaning that you should be able to run this code against any active workskeet you like, regardless of its name.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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