好文分享

【EXCEL VBA】調整買賣日報表位置

by 投行大叔

Sub 調整日報表位置()
‘
‘ fast 巨集
‘

‘
Range(“G4”).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveWindow.SmallScroll Down:=24
Range(“R30”).Select
ActiveWindow.LargeScroll Down:=1
Range(“R50”).Select
ActiveWindow.SmallScroll Down:=15
ActiveWindow.LargeScroll Down:=37
ActiveWindow.SmallScroll Down:=9
Range(“A823”).Select
ActiveWindow.SmallScroll Down:=168
Range(“A3001”).Select
ActiveWindow.ScrollRow = 2985
ActiveWindow.ScrollRow = 2978
ActiveWindow.ScrollRow = 2971
ActiveWindow.ScrollRow = 2964
ActiveWindow.ScrollRow = 2942
ActiveWindow.ScrollRow = 2928
ActiveWindow.ScrollRow = 2921
ActiveWindow.ScrollRow = 2899
ActiveWindow.ScrollRow = 2892
ActiveWindow.ScrollRow = 2885
ActiveWindow.ScrollRow = 2870
ActiveWindow.ScrollRow = 2863
ActiveWindow.ScrollRow = 2827
ActiveWindow.ScrollRow = 2813
ActiveWindow.ScrollRow = 2798
ActiveWindow.ScrollRow = 2784
ActiveWindow.ScrollRow = 2762
ActiveWindow.ScrollRow = 2726
ActiveWindow.ScrollRow = 2662
ActiveWindow.ScrollRow = 2604
ActiveWindow.ScrollRow = 2590
ActiveWindow.ScrollRow = 2561
ActiveWindow.ScrollRow = 2496
ActiveWindow.ScrollRow = 2360
ActiveWindow.ScrollRow = 2324
ActiveWindow.ScrollRow = 2280
ActiveWindow.ScrollRow = 2252
ActiveWindow.ScrollRow = 2158
ActiveWindow.ScrollRow = 1978
ActiveWindow.ScrollRow = 1906
ActiveWindow.ScrollRow = 1885
ActiveWindow.ScrollRow = 1871
ActiveWindow.ScrollRow = 1842
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1734
ActiveWindow.ScrollRow = 1626
ActiveWindow.ScrollRow = 1568
ActiveWindow.ScrollRow = 1331
ActiveWindow.ScrollRow = 1274
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1123
ActiveWindow.ScrollRow = 1015
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 813
ActiveWindow.ScrollRow = 792
ActiveWindow.ScrollRow = 698
ActiveWindow.ScrollRow = 677
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 562
ActiveWindow.ScrollRow = 547
ActiveWindow.ScrollRow = 540
ActiveWindow.ScrollRow = 526
ActiveWindow.ScrollRow = 511
ActiveWindow.ScrollRow = 475
ActiveWindow.ScrollRow = 454
ActiveWindow.ScrollRow = 432
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 375
ActiveWindow.ScrollRow = 346
ActiveWindow.ScrollRow = 339
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 1
Range(“G4”).Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 636
ActiveWindow.ScrollRow = 690
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 773
ActiveWindow.ScrollRow = 793
Range(“K811”).Select
ActiveWindow.SmallScroll Down:=-105
ActiveWindow.ScrollRow = 682
ActiveWindow.ScrollRow = 680
ActiveWindow.ScrollRow = 673
ActiveWindow.ScrollRow = 661
ActiveWindow.ScrollRow = 646
ActiveWindow.ScrollRow = 633
ActiveWindow.ScrollRow = 574
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 408
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 1
Range(“G4:K5000”).Select
Application.CutCopyMode = False
Selection.Cut
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 349
ActiveWindow.ScrollRow = 446
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 579
ActiveWindow.ScrollRow = 589
ActiveWindow.ScrollRow = 623
ActiveWindow.ScrollRow = 633
ActiveWindow.ScrollRow = 686
ActiveWindow.ScrollRow = 711
ActiveWindow.ScrollRow = 718
ActiveWindow.ScrollRow = 732
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 754
ActiveWindow.ScrollRow = 772
ActiveWindow.ScrollRow = 777
ActiveWindow.ScrollRow = 785
ActiveWindow.ScrollRow = 791
ActiveWindow.ScrollRow = 793
ActiveWindow.SmallScroll Down:=54
Range(“B1907”).Select
Application.Goto Reference:=”R5001C1″
Range(“A5001”).Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 4989
ActiveWindow.ScrollRow = 4941
ActiveWindow.ScrollRow = 4917
ActiveWindow.ScrollRow = 4461
ActiveWindow.ScrollRow = 4341
ActiveWindow.ScrollRow = 4222
ActiveWindow.ScrollRow = 3790
ActiveWindow.ScrollRow = 3718
ActiveWindow.ScrollRow = 2807
ActiveWindow.ScrollRow = 2447
ActiveWindow.ScrollRow = 2255
ActiveWindow.ScrollRow = 1559
ActiveWindow.ScrollRow = 1320
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 1
Range(“A4:E5001”).Select
Range(“A5001”).Activate
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 888
ActiveWindow.ScrollRow = 984
ActiveWindow.ScrollRow = 1056
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1368
ActiveWindow.ScrollRow = 1392
ActiveWindow.ScrollRow = 1511
ActiveWindow.ScrollRow = 1559
ActiveWindow.ScrollRow = 1751
ActiveWindow.ScrollRow = 1775
ActiveWindow.ScrollRow = 1895
ActiveWindow.ScrollRow = 1919
ActiveWindow.ScrollRow = 2183
ActiveWindow.ScrollRow = 2255
ActiveWindow.ScrollRow = 2399
ActiveWindow.ScrollRow = 2495
ActiveWindow.ScrollRow = 3094
ActiveWindow.ScrollRow = 3142
ActiveWindow.ScrollRow = 3166
ActiveWindow.ScrollRow = 3190
ActiveWindow.ScrollRow = 3286
ActiveWindow.ScrollRow = 3358
ActiveWindow.ScrollRow = 3454
ActiveWindow.ScrollRow = 3574
ActiveWindow.ScrollRow = 3622
ActiveWindow.ScrollRow = 3718
ActiveWindow.ScrollRow = 3742
ActiveWindow.ScrollRow = 3766
ActiveWindow.ScrollRow = 3790
ActiveWindow.ScrollRow = 3862
ActiveWindow.ScrollRow = 3886
ActiveWindow.ScrollRow = 4006
ActiveWindow.ScrollRow = 4126
ActiveWindow.ScrollRow = 4509
ActiveWindow.ScrollRow = 4557
ActiveWindow.ScrollRow = 4677
ActiveWindow.ScrollRow = 4701
ActiveWindow.ScrollRow = 4797
ActiveWindow.ScrollRow = 4869
ActiveWindow.ScrollRow = 5013
ActiveWindow.ScrollRow = 5109
ActiveWindow.ScrollRow = 5277
ActiveWindow.ScrollRow = 5397
ActiveWindow.ScrollRow = 5445
ActiveWindow.ScrollRow = 5469
ActiveWindow.ScrollRow = 5493
ActiveWindow.ScrollRow = 5589
ActiveWindow.ScrollRow = 5613
ActiveWindow.ScrollRow = 5661
ActiveWindow.ScrollRow = 5732
ActiveWindow.ScrollRow = 5756
ActiveWindow.ScrollRow = 5780
ActiveWindow.ScrollRow = 5804
ActiveWindow.ScrollRow = 5780
ActiveWindow.ScrollRow = 5756
ActiveWindow.ScrollRow = 5732
ActiveWindow.ScrollRow = 5709
ActiveWindow.ScrollRow = 5685
ActiveWindow.ScrollRow = 5661
ActiveWindow.ScrollRow = 5613
ActiveWindow.ScrollRow = 5565
ActiveWindow.ScrollRow = 5469
ActiveWindow.ScrollRow = 5373
ActiveWindow.ScrollRow = 5349
ActiveWindow.ScrollRow = 5301
ActiveWindow.ScrollRow = 5277
ActiveWindow.ScrollRow = 5229
ActiveWindow.ScrollRow = 5205
ActiveWindow.ScrollRow = 5157
ActiveWindow.ScrollRow = 5133
ActiveWindow.ScrollRow = 5037
ActiveWindow.ScrollRow = 5013
ActiveWindow.ScrollRow = 4965
ActiveWindow.ScrollRow = 4941
ActiveWindow.ScrollRow = 4893
ActiveWindow.ScrollRow = 4869
ActiveWindow.ScrollRow = 4821
ActiveWindow.ScrollRow = 4797
ActiveWindow.ScrollRow = 4749
ActiveWindow.ScrollRow = 4725
ActiveWindow.ScrollRow = 4677
ActiveWindow.ScrollRow = 4653
ActiveWindow.ScrollRow = 4701
ActiveWindow.ScrollRow = 4725
ActiveWindow.ScrollRow = 4773
ActiveWindow.ScrollRow = 4797
ActiveWindow.ScrollRow = 4821
ActiveWindow.ScrollRow = 4869
ActiveWindow.ScrollRow = 4917
ActiveWindow.ScrollRow = 4965
ActiveWindow.ScrollRow = 4989
ActiveWindow.ScrollRow = 5085
ActiveWindow.ScrollRow = 5109
ActiveWindow.ScrollRow = 5061
ActiveWindow.ScrollRow = 5037
ActiveWindow.ScrollRow = 5013
ActiveWindow.ScrollRow = 4965
ActiveWindow.ScrollRow = 4966
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
ActiveWindow.ScrollRow = 816
ActiveWindow.ScrollRow = 792
ActiveWindow.ScrollRow = 744
ActiveWindow.ScrollRow = 720
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 624
ActiveWindow.ScrollRow = 528
ActiveWindow.ScrollRow = 504
ActiveWindow.ScrollRow = 480
ActiveWindow.ScrollRow = 384
ActiveWindow.ScrollRow = 360
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 1
Range(“C2”).Select
Columns(“B:B”).EntireColumn.AutoFit

Range(“A3:E10000”).Select
Selection.Sort Key1:=Range(“A3”), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlStroke, DataOption1:=xlSortNormal

ActiveSheet.Rows(1).Delete
ActiveSheet.Rows(1).Delete

ActiveSheet.Columns(7).Delete
ActiveSheet.Columns(7).Delete
ActiveSheet.Columns(7).Delete
ActiveSheet.Columns(7).Delete
ActiveSheet.Columns(7).Delete

Range(“A1:E1”).Select
Range(“E1”).Activate
Selection.AutoFilter

End Sub