Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: record Macro..Save As..but Filename already exist..Replace YES

  1. #1
    Join Date
    25th November 2003
    Posts
    99

    record Macro..Save As..but Filename already exist..Replace YES

    Hi the experts,

    I was creating a Macro and face this SaveAs problem.. I illustrate with example below.

    1) I already have a standard Excel file in C:\ as "Template.xls"
    2) I recorded a macro on my excel "Template.xls" and copy-paste all the formulas as values and saved it as filename "Status (reviewed).xls"
    3) I then re-type all the formulas in the active worksheet "Status (reviewed).xls" and saved it back in C:\ as "Template.xls"
    4) then I stopped the macro recording.
    5) I used Private Sub Workbook_Open() function in VB "ThisWorkbook"to auto-start the above macro in "Template.xls".
    6) The macro auto-load works but before it could complete End Sub, it prompts me something like this: '"A file named 'C:\foldername\filename.xls' already exists in this location. Do you want to replace it ? It asks with three buttons -YES/NO/Cancel". The default button is at NO.

    I had to click YES twice for the 2 SaveAs in order to continue the macro action. What should I add/change in the code to automatically continue by allowing YES ?

    I attached the sample file. Please help me.
    Thanks a lot in advance.
    Attached Images
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,354
    Put the line

    Application.DisplayAlerts = False

    in your code and the file will be saved without the message showing (even though the default was no, the file will still be saved).

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th November 2003
    Posts
    99
    Hi Derk,

    The Private Sub Workbook_Open() below in "ThisWorkbook" runs fine and completed all the automated tasks when I opened the file "UMCi critical list stocking status & ETA - template.xls".

    I use macro Action "Autoexec" in MSAccess, using RunApp to run this directory path & filename in the following Command line:

    "C:\Program Files\Microsoft Office\Office10\excel.exe" "C:\(RSL)300mm-200mm\UMCi Critical List Stocking Status\UMCi critical list stocking status & ETA - template.xls"

    The Autoexec RunApp did open the above Excel file but stopped at this line:
    Windows("AccessDownload for UMCi critical list.xls").Activate
    just after the line ...ReplaceFormat:=False

    Please help me where is the bug and what should I amend.
    Thanks.



    Code

    Private Sub Workbook_Open()

    ' Sub MacroUmciCriticalListStockingUpdate()
    '
    ' MacroUmciCriticalListStockingUpdate Macro
    ' Macro recorded 9/10/2004 by Applied Materials
    '

    '
    Sheets("Stocking Status &ETA").Select
    ChDir "C:\(RSL)300mm-200mm\UMCi Critical List Stocking Status"
    Workbooks.Open Filename:= _
    "C:\(RSL)300mm-200mm\UMCi Critical List Stocking Status\AccessDownload for UMCi critical list.xls"
    Windows("UMCi critical list stocking status & ETA - template.xls").Activate
    ' Windows("AccessDownload for UMCi critical list.xls").Activate
    ' Range("K2:N1875").Select
    Selection.Copy
    Windows("UMCi critical list stocking status & ETA - template.xls").Activate
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Windows("AccessDownload for UMCi critical list.xls").Activate
    Range("O2:Q1875").Select
    Range("O1875").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("UMCi critical list stocking status & ETA - template.xls").Activate
    Range("R2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Windows("AccessDownload for UMCi critical list.xls").Activate
    Range("R2:S1875").Select
    ActiveWindow.SmallScroll Down:=-1
    Application.CutCopyMode = False
    Selection.Copy
    Windows("UMCi critical list stocking status & ETA - template.xls").Activate
    Range("V2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Windows("AccessDownload for UMCi critical list.xls").Activate
    Range("T2:W1875").Select
    Range("T1875").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("UMCi critical list stocking status & ETA - template.xls").Activate
    Range("Y2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AC2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=FillRemarks(RC[-28])"
    Range("AC2").Select
    Selection.Copy
    Range("AC3").Select
    ActiveWindow.LargeScroll Down:=8
    ActiveWindow.ScrollRow = 271
    ActiveWindow.ScrollRow = 274
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 289
    ActiveWindow.ScrollRow = 293
    ActiveWindow.ScrollRow = 296
    ActiveWindow.ScrollRow = 300
    ActiveWindow.ScrollRow = 304
    ActiveWindow.ScrollRow = 307
    ActiveWindow.ScrollRow = 311
    ActiveWindow.ScrollRow = 315
    ActiveWindow.ScrollRow = 318
    ActiveWindow.ScrollRow = 322
    ActiveWindow.ScrollRow = 326
    ActiveWindow.ScrollRow = 329
    ActiveWindow.ScrollRow = 333
    ActiveWindow.ScrollRow = 337
    ActiveWindow.ScrollRow = 340
    ActiveWindow.ScrollRow = 344
    ActiveWindow.ScrollRow = 348
    ActiveWindow.ScrollRow = 351
    ActiveWindow.ScrollRow = 355
    ActiveWindow.ScrollRow = 359
    ActiveWindow.ScrollRow = 362
    ActiveWindow.ScrollRow = 366
    ActiveWindow.ScrollRow = 370
    ActiveWindow.ScrollRow = 373
    ActiveWindow.ScrollRow = 377
    ActiveWindow.ScrollRow = 381
    ActiveWindow.ScrollRow = 385
    ActiveWindow.ScrollRow = 388
    ActiveWindow.ScrollRow = 392
    ActiveWindow.ScrollRow = 396
    ActiveWindow.ScrollRow = 399
    ActiveWindow.ScrollRow = 403
    ActiveWindow.ScrollRow = 407
    ActiveWindow.ScrollRow = 410
    ActiveWindow.ScrollRow = 414
    ActiveWindow.ScrollRow = 418
    ActiveWindow.ScrollRow = 421
    ActiveWindow.ScrollRow = 425
    ActiveWindow.ScrollRow = 429
    ActiveWindow.ScrollRow = 432
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 440
    ActiveWindow.ScrollRow = 443
    ActiveWindow.ScrollRow = 447
    ActiveWindow.ScrollRow = 451
    ActiveWindow.ScrollRow = 454
    ActiveWindow.ScrollRow = 458
    ActiveWindow.ScrollRow = 462
    ActiveWindow.ScrollRow = 465
    ActiveWindow.ScrollRow = 469
    ActiveWindow.ScrollRow = 473
    ActiveWindow.ScrollRow = 476
    ActiveWindow.ScrollRow = 480
    ActiveWindow.ScrollRow = 484
    ActiveWindow.ScrollRow = 488
    ActiveWindow.ScrollRow = 491
    ActiveWindow.ScrollRow = 495
    ActiveWindow.ScrollRow = 499
    ActiveWindow.ScrollRow = 502
    ActiveWindow.ScrollRow = 506
    ActiveWindow.ScrollRow = 510
    ActiveWindow.ScrollRow = 513
    ActiveWindow.ScrollRow = 517
    ActiveWindow.ScrollRow = 521
    ActiveWindow.ScrollRow = 524
    ActiveWindow.ScrollRow = 528
    ActiveWindow.ScrollRow = 532
    ActiveWindow.ScrollRow = 535
    ActiveWindow.ScrollRow = 539
    ActiveWindow.ScrollRow = 543
    ActiveWindow.ScrollRow = 546
    ActiveWindow.ScrollRow = 550
    ActiveWindow.ScrollRow = 554
    ActiveWindow.ScrollRow = 557
    ActiveWindow.ScrollRow = 565
    ActiveWindow.ScrollRow = 568
    ActiveWindow.ScrollRow = 572
    ActiveWindow.ScrollRow = 576
    ActiveWindow.ScrollRow = 583
    ActiveWindow.ScrollRow = 587
    ActiveWindow.ScrollRow = 594
    ActiveWindow.ScrollRow = 598
    ActiveWindow.ScrollRow = 602
    ActiveWindow.ScrollRow = 605
    ActiveWindow.ScrollRow = 609
    ActiveWindow.ScrollRow = 613
    ActiveWindow.ScrollRow = 616
    ActiveWindow.ScrollRow = 620
    ActiveWindow.ScrollRow = 624
    ActiveWindow.ScrollRow = 627
    ActiveWindow.ScrollRow = 631
    ActiveWindow.ScrollRow = 635
    ActiveWindow.ScrollRow = 638
    ActiveWindow.ScrollRow = 642
    ActiveWindow.ScrollRow = 646
    ActiveWindow.ScrollRow = 649
    ActiveWindow.ScrollRow = 653
    ActiveWindow.ScrollRow = 657
    ActiveWindow.ScrollRow = 660
    ActiveWindow.ScrollRow = 668
    ActiveWindow.ScrollRow = 671
    ActiveWindow.ScrollRow = 675
    ActiveWindow.ScrollRow = 679
    ActiveWindow.ScrollRow = 690
    ActiveWindow.ScrollRow = 693
    ActiveWindow.ScrollRow = 701
    ActiveWindow.ScrollRow = 705
    ActiveWindow.ScrollRow = 708
    ActiveWindow.ScrollRow = 712
    ActiveWindow.ScrollRow = 716
    ActiveWindow.ScrollRow = 719
    ActiveWindow.ScrollRow = 723
    ActiveWindow.ScrollRow = 730
    ActiveWindow.ScrollRow = 734
    ActiveWindow.ScrollRow = 738
    ActiveWindow.ScrollRow = 741
    ActiveWindow.ScrollRow = 745
    ActiveWindow.ScrollRow = 749
    ActiveWindow.ScrollRow = 752
    ActiveWindow.ScrollRow = 756
    ActiveWindow.ScrollRow = 760
    ActiveWindow.ScrollRow = 763
    ActiveWindow.ScrollRow = 767
    ActiveWindow.ScrollRow = 771
    ActiveWindow.ScrollRow = 774
    ActiveWindow.ScrollRow = 778
    ActiveWindow.ScrollRow = 782
    ActiveWindow.ScrollRow = 785
    ActiveWindow.ScrollRow = 789
    ActiveWindow.ScrollRow = 793
    ActiveWindow.ScrollRow = 796
    ActiveWindow.ScrollRow = 800
    ActiveWindow.ScrollRow = 804
    ActiveWindow.ScrollRow = 808
    ActiveWindow.ScrollRow = 811
    ActiveWindow.ScrollRow = 815
    ActiveWindow.ScrollRow = 819
    ActiveWindow.ScrollRow = 822
    ActiveWindow.ScrollRow = 826
    ActiveWindow.ScrollRow = 830
    ActiveWindow.ScrollRow = 833
    ActiveWindow.ScrollRow = 837
    ActiveWindow.ScrollRow = 841
    ActiveWindow.ScrollRow = 844
    ActiveWindow.ScrollRow = 848
    ActiveWindow.ScrollRow = 852
    ActiveWindow.ScrollRow = 855
    ActiveWindow.ScrollRow = 859
    ActiveWindow.ScrollRow = 863
    ActiveWindow.ScrollRow = 866
    ActiveWindow.ScrollRow = 870
    ActiveWindow.ScrollRow = 874
    ActiveWindow.ScrollRow = 877
    ActiveWindow.ScrollRow = 881
    ActiveWindow.ScrollRow = 885
    ActiveWindow.ScrollRow = 888
    ActiveWindow.ScrollRow = 892
    ActiveWindow.ScrollRow = 896
    ActiveWindow.ScrollRow = 899
    ActiveWindow.ScrollRow = 903
    ActiveWindow.ScrollRow = 907
    ActiveWindow.ScrollRow = 911
    ActiveWindow.ScrollRow = 914
    ActiveWindow.ScrollRow = 918
    ActiveWindow.ScrollRow = 922
    ActiveWindow.ScrollRow = 925
    ActiveWindow.ScrollRow = 929
    ActiveWindow.ScrollRow = 933
    ActiveWindow.ScrollRow = 936
    ActiveWindow.ScrollRow = 940
    ActiveWindow.ScrollRow = 944
    ActiveWindow.ScrollRow = 947
    ActiveWindow.ScrollRow = 951
    ActiveWindow.ScrollRow = 955
    ActiveWindow.ScrollRow = 958
    ActiveWindow.ScrollRow = 962
    ActiveWindow.ScrollRow = 966
    ActiveWindow.ScrollRow = 969
    ActiveWindow.ScrollRow = 973
    ActiveWindow.ScrollRow = 977
    ActiveWindow.ScrollRow = 980
    ActiveWindow.ScrollRow = 984
    ActiveWindow.ScrollRow = 988
    ActiveWindow.ScrollRow = 991
    ActiveWindow.ScrollRow = 999
    ActiveWindow.ScrollRow = 1002
    ActiveWindow.ScrollRow = 1010
    ActiveWindow.ScrollRow = 1013
    ActiveWindow.ScrollRow = 1017
    ActiveWindow.ScrollRow = 1021
    ActiveWindow.ScrollRow = 1025
    ActiveWindow.ScrollRow = 1028
    ActiveWindow.ScrollRow = 1032
    ActiveWindow.ScrollRow = 1036
    ActiveWindow.ScrollRow = 1039
    ActiveWindow.ScrollRow = 1043
    ActiveWindow.ScrollRow = 1047
    ActiveWindow.ScrollRow = 1050
    ActiveWindow.ScrollRow = 1054
    ActiveWindow.ScrollRow = 1058
    ActiveWindow.ScrollRow = 1061
    ActiveWindow.ScrollRow = 1065
    ActiveWindow.ScrollRow = 1069
    ActiveWindow.ScrollRow = 1072
    ActiveWindow.ScrollRow = 1076
    ActiveWindow.ScrollRow = 1080
    ActiveWindow.ScrollRow = 1083
    ActiveWindow.ScrollRow = 1087
    ActiveWindow.ScrollRow = 1091
    ActiveWindow.ScrollRow = 1094
    ActiveWindow.ScrollRow = 1098
    ActiveWindow.ScrollRow = 1102
    ActiveWindow.ScrollRow = 1105
    ActiveWindow.ScrollRow = 1109
    ActiveWindow.ScrollRow = 1113
    ActiveWindow.ScrollRow = 1116
    ActiveWindow.ScrollRow = 1120
    ActiveWindow.ScrollRow = 1124
    ActiveWindow.ScrollRow = 1128
    ActiveWindow.ScrollRow = 1131
    ActiveWindow.ScrollRow = 1135
    ActiveWindow.ScrollRow = 1139
    ActiveWindow.ScrollRow = 1142
    ActiveWindow.ScrollRow = 1146
    ActiveWindow.ScrollRow = 1150
    ActiveWindow.ScrollRow = 1153
    ActiveWindow.ScrollRow = 1157
    ActiveWindow.ScrollRow = 1161
    ActiveWindow.ScrollRow = 1164
    ActiveWindow.ScrollRow = 1168
    ActiveWindow.ScrollRow = 1172
    ActiveWindow.ScrollRow = 1175
    ActiveWindow.ScrollRow = 1179
    ActiveWindow.ScrollRow = 1183
    ActiveWindow.ScrollRow = 1186
    ActiveWindow.ScrollRow = 1190
    ActiveWindow.ScrollRow = 1194
    ActiveWindow.ScrollRow = 1197
    ActiveWindow.ScrollRow = 1201
    ActiveWindow.ScrollRow = 1205
    ActiveWindow.ScrollRow = 1208
    ActiveWindow.ScrollRow = 1212
    ActiveWindow.ScrollRow = 1216
    ActiveWindow.ScrollRow = 1219
    ActiveWindow.ScrollRow = 1223
    ActiveWindow.ScrollRow = 1227
    ActiveWindow.ScrollRow = 1231
    ActiveWindow.ScrollRow = 1234
    ActiveWindow.ScrollRow = 1238
    ActiveWindow.ScrollRow = 1242
    ActiveWindow.ScrollRow = 1245
    ActiveWindow.ScrollRow = 1249
    ActiveWindow.ScrollRow = 1253
    ActiveWindow.ScrollRow = 1256
    ActiveWindow.ScrollRow = 1260
    ActiveWindow.ScrollRow = 1264
    ActiveWindow.ScrollRow = 1267
    ActiveWindow.ScrollRow = 1271
    ActiveWindow.ScrollRow = 1275
    ActiveWindow.ScrollRow = 1278
    ActiveWindow.ScrollRow = 1282
    ActiveWindow.ScrollRow = 1286
    ActiveWindow.ScrollRow = 1289
    ActiveWindow.ScrollRow = 1293
    ActiveWindow.ScrollRow = 1297
    ActiveWindow.ScrollRow = 1300
    ActiveWindow.ScrollRow = 1304
    ActiveWindow.ScrollRow = 1308
    ActiveWindow.ScrollRow = 1311
    ActiveWindow.ScrollRow = 1315
    ActiveWindow.ScrollRow = 1319
    ActiveWindow.ScrollRow = 1322
    ActiveWindow.ScrollRow = 1326
    ActiveWindow.ScrollRow = 1330
    ActiveWindow.ScrollRow = 1333
    ActiveWindow.ScrollRow = 1337
    ActiveWindow.ScrollRow = 1341
    ActiveWindow.ScrollRow = 1345
    ActiveWindow.ScrollRow = 1348
    ActiveWindow.ScrollRow = 1352
    ActiveWindow.ScrollRow = 1356
    ActiveWindow.ScrollRow = 1359
    ActiveWindow.ScrollRow = 1363
    ActiveWindow.ScrollRow = 1367
    ActiveWindow.ScrollRow = 1370
    ActiveWindow.ScrollRow = 1374
    ActiveWindow.ScrollRow = 1378
    ActiveWindow.ScrollRow = 1381
    ActiveWindow.ScrollRow = 1385
    ActiveWindow.ScrollRow = 1389
    ActiveWindow.ScrollRow = 1392
    ActiveWindow.ScrollRow = 1396
    ActiveWindow.ScrollRow = 1400
    ActiveWindow.ScrollRow = 1403
    ActiveWindow.ScrollRow = 1407
    ActiveWindow.ScrollRow = 1411
    ActiveWindow.ScrollRow = 1414
    ActiveWindow.ScrollRow = 1418
    ActiveWindow.ScrollRow = 1422
    ActiveWindow.ScrollRow = 1425
    ActiveWindow.ScrollRow = 1429
    ActiveWindow.ScrollRow = 1433
    ActiveWindow.ScrollRow = 1436
    ActiveWindow.ScrollRow = 1440
    ActiveWindow.ScrollRow = 1444
    ActiveWindow.ScrollRow = 1448
    ActiveWindow.ScrollRow = 1451
    ActiveWindow.ScrollRow = 1455
    ActiveWindow.ScrollRow = 1459
    ActiveWindow.ScrollRow = 1462
    ActiveWindow.ScrollRow = 1466
    ActiveWindow.ScrollRow = 1470
    ActiveWindow.ScrollRow = 1473
    ActiveWindow.ScrollRow = 1481
    ActiveWindow.ScrollRow = 1484
    ActiveWindow.ScrollRow = 1492
    ActiveWindow.ScrollRow = 1495
    ActiveWindow.ScrollRow = 1503
    ActiveWindow.ScrollRow = 1506
    ActiveWindow.ScrollRow = 1510
    ActiveWindow.ScrollRow = 1514
    ActiveWindow.ScrollRow = 1517
    ActiveWindow.ScrollRow = 1521
    ActiveWindow.ScrollRow = 1525
    ActiveWindow.ScrollRow = 1528
    ActiveWindow.ScrollRow = 1536
    ActiveWindow.ScrollRow = 1539
    ActiveWindow.ScrollRow = 1543
    ActiveWindow.ScrollRow = 1547
    ActiveWindow.ScrollRow = 1551
    ActiveWindow.ScrollRow = 1554
    ActiveWindow.ScrollRow = 1558
    ActiveWindow.ScrollRow = 1562
    ActiveWindow.ScrollRow = 1565
    ActiveWindow.ScrollRow = 1569
    ActiveWindow.ScrollRow = 1573
    ActiveWindow.ScrollRow = 1576
    ActiveWindow.ScrollRow = 1580
    ActiveWindow.ScrollRow = 1584
    ActiveWindow.ScrollRow = 1587
    ActiveWindow.ScrollRow = 1591
    ActiveWindow.ScrollRow = 1595
    ActiveWindow.ScrollRow = 1598
    ActiveWindow.ScrollRow = 1602
    ActiveWindow.ScrollRow = 1606
    ActiveWindow.ScrollRow = 1609
    ActiveWindow.ScrollRow = 1613
    ActiveWindow.ScrollRow = 1617
    ActiveWindow.ScrollRow = 1620
    ActiveWindow.ScrollRow = 1624
    ActiveWindow.ScrollRow = 1628
    ActiveWindow.ScrollRow = 1631
    ActiveWindow.ScrollRow = 1635
    ActiveWindow.ScrollRow = 1639
    ActiveWindow.ScrollRow = 1642
    ActiveWindow.ScrollRow = 1646
    ActiveWindow.ScrollRow = 1650
    ActiveWindow.ScrollRow = 1653
    ActiveWindow.ScrollRow = 1657
    ActiveWindow.ScrollRow = 1661
    ActiveWindow.ScrollRow = 1665
    ActiveWindow.ScrollRow = 1668
    ActiveWindow.ScrollRow = 1672
    ActiveWindow.ScrollRow = 1676
    ActiveWindow.ScrollRow = 1679
    ActiveWindow.ScrollRow = 1683
    ActiveWindow.ScrollRow = 1687
    ActiveWindow.ScrollRow = 1690
    ActiveWindow.ScrollRow = 1694
    ActiveWindow.ScrollRow = 1698
    ActiveWindow.ScrollRow = 1701
    ActiveWindow.ScrollRow = 1705
    ActiveWindow.ScrollRow = 1712
    ActiveWindow.ScrollRow = 1716
    ActiveWindow.ScrollRow = 1720
    ActiveWindow.ScrollRow = 1727
    ActiveWindow.ScrollRow = 1731
    ActiveWindow.ScrollRow = 1734
    ActiveWindow.ScrollRow = 1738
    ActiveWindow.ScrollRow = 1742
    ActiveWindow.ScrollRow = 1745
    ActiveWindow.ScrollRow = 1753
    ActiveWindow.ScrollRow = 1756
    ActiveWindow.ScrollRow = 1760
    ActiveWindow.ScrollRow = 1764
    ActiveWindow.ScrollRow = 1768
    ActiveWindow.ScrollRow = 1771
    ActiveWindow.ScrollRow = 1775
    ActiveWindow.ScrollRow = 1779
    ActiveWindow.ScrollRow = 1782
    ActiveWindow.ScrollRow = 1786
    ActiveWindow.ScrollRow = 1790
    ActiveWindow.ScrollRow = 1793
    ActiveWindow.ScrollRow = 1797
    ActiveWindow.ScrollRow = 1801
    ActiveWindow.ScrollRow = 1804
    ActiveWindow.ScrollRow = 1808
    ActiveWindow.ScrollRow = 1812
    ActiveWindow.ScrollRow = 1815
    ActiveWindow.ScrollRow = 1819
    ActiveWindow.ScrollRow = 1823
    ActiveWindow.ScrollRow = 1826
    ActiveWindow.ScrollRow = 1830
    ActiveWindow.ScrollRow = 1834
    ActiveWindow.ScrollRow = 1837
    ActiveWindow.ScrollRow = 1841
    ActiveWindow.ScrollRow = 1845
    ActiveWindow.ScrollRow = 1848
    ActiveWindow.ScrollRow = 1852
    ActiveWindow.ScrollRow = 1856
    ActiveWindow.ScrollRow = 1859
    ActiveWindow.ScrollRow = 1863
    Range("AC3:AC1875").Select
    ActiveSheet.Paste
    Range("AC2:AC1875").Select
    Range("AC1875").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AF1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[1]+7"
    Range("AF2").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select
    Selection.AutoFilter
    ActiveWindow.LargeScroll ToRight:=-1
    Selection.AutoFilter Field:=5, Criteria1:="=*CMP*", Operator:=xlAnd
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("F5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16
    Selection.AutoFilter Field:=17, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("Q1").Select
    Selection.AutoFilter Field:=17
    Range("E1").Select
    Selection.AutoFilter Field:=5, Criteria1:="=*CPI*", Operator:=xlAnd
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("F6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16
    Range("Q1").Select
    Selection.AutoFilter Field:=17, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("Q1").Select
    Selection.AutoFilter Field:=17
    Range("E1").Select
    Selection.AutoFilter Field:=5, Criteria1:="=*DSM*", Operator:=xlAnd
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("F7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16
    Range("Q1").Select
    Selection.AutoFilter Field:=17, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("D7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("Q1").Select
    Selection.AutoFilter Field:=17
    Range("E1").Select
    Selection.AutoFilter Field:=5, Criteria1:="=*FEP*", Operator:=xlAnd
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("C8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("F8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16
    Range("Q1").Select
    Selection.AutoFilter Field:=17, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("D8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("Q1").Select
    Selection.AutoFilter Field:=17
    Range("E1").Select
    Selection.AutoFilter Field:=5, Criteria1:="=*ETCH*", Operator:=xlAnd
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("F9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Stocking Status &ETA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=16
    Range("Q1").Select
    Selection.AutoFilter Field:=17, Criteria1:="Yes"
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cover&Fill Rates").Select
    Range("D9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[10]C+7"
    Range("D3:G3").Select
    Sheets("Stocking Status &ETA").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("AF1").Select
    Selection.AutoFilter Field:=32, Criteria1:="<>"

    Application.DisplayAlerts = False 'avoid alert _
    ActiveWorkbook.SaveAs Filename:= _
    "C:\(RSL)300mm-200mm\UMCi Critical List Stocking Status\UMCi critical list stocking status & ETA (reviewed).xls" _
    , FileFormat:=xlNormal
    Application.DisplayAlerts = False 'avoid alert

    Selection.AutoFilter
    Selection.EntireColumn.Insert
    Columns("AG:AG").Select
    Selection.Copy
    Range("AF1").Select
    ActiveSheet.Paste
    Range("AF1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("AC2:AC1875").Select
    Selection.ClearContents
    Range("Y2:AB1875").Select
    Range("Y1875").Activate
    Selection.ClearContents
    Range("V2:W1875").Select
    Selection.ClearContents
    Range("R2:T1875").Select
    Range("R1875").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll ToRight:=-13
    Range("K2:N1875").Select
    Selection.ClearContents
    Range("A2").Select
    Sheets("Cover&Fill Rates").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Rows("2:6").Select
    Range("A6").Activate
    Selection.Insert Shift:=xlDown
    Range("A12:G20").Select
    Selection.Copy
    Range("A2").Select
    ActiveSheet.Paste
    Range("C5:G10").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("G2").Select
    Selection.ClearContents
    Windows("AccessDownload for UMCi critical list.xls").Activate
    Range("I1").Select
    ActiveWorkbook.Close
    Sheets("Stocking Status &ETA").Select
    ActiveWindow.SmallScroll ToRight:=0

    ActiveWorkbook.SaveAs Filename:= _
    "C:\(RSL)300mm-200mm\UMCi Critical List Stocking Status\UMCi critical list stocking status & ETA - template.xls" _
    , FileFormat:=xlNormal
    Application.DisplayAlerts = False

    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,354
    I haven't carefully read each line of your code (you can delete a lot of it that the macro recorder put in), but I would guess the workbook "AccessDownload for UMCi critical list.xls" is no longer there. When the macro stops on the error, what then are the names of the open Excel windows?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th November 2003
    Posts
    99
    Hi Derk,

    "AccessDownload for UMCi critical list.xls" and " "UMCi critical list stocking status & ETA - template.xls" are still the two open Excel when the error stops at :

    Windows("AccessDownload for UMCi critical list.xls").Activate

    just after the line ...ReplaceFormat:=False

    Sorry to ask your help again.
    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,354
    I don't know that I have any help to offer without actually running your macro in your setup. You might try using
    Workbooks("AccessDownload for UMCi critical list.xls").Activate
    instead.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Save As: Filename -and- Errors
    By mellojoe in forum EXCEL HELP
    Replies: 7
    Last Post: April 4th, 2007, 06:06
  2. Macro: Word Save As Filename
    By Slims in forum Excel and/or Word Help
    Replies: 1
    Last Post: December 26th, 2006, 07:43
  3. Save also a standard filename whenever use File>Save As
    By alienscript in forum EXCEL HELP
    Replies: 4
    Last Post: December 3rd, 2004, 05:51
  4. Replies: 1
    Last Post: November 4th, 2004, 15:26
  5. [Solved] VBA: Save as filename
    By lulu in forum EXCEL HELP
    Replies: 6
    Last Post: August 30th, 2003, 08:32

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno