Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 Files

  • #2
    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).
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      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

      Comment


      • #4
        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?
        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

        Comment


        • #5
          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

          Comment


          • #6
            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.
            Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X