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

[alienscript]

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.

[Derk]

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).

[alienscript]

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

[Derk]

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?

[alienscript]

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

[Derk]

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.