请求修改VBA宏实现无提示覆盖另一工作簿数据

Got it! To eliminate that annoying overwrite confirmation prompt when pasting data into your target workbook, you just need to temporarily disable Excel's display alerts during the operation. Here's how to modify your existing macro:

Modified Original Code #

Sub AV() 
    ' Disable alerts to suppress overwrite confirmation
    Application.DisplayAlerts = False
    
    Workbooks.Open Filename:="T:\Cleveland\Avon\Monthly Sales\Monthly Sales 2018.xls"
    Windows("Sales_By_Day_Location Analysis.xlsm").Activate
    Sheets("AV").Select
    Range("A1:AC88").Copy
    Windows("Monthly Sales 2018.xls").Activate
    Sheets("Avon").Select
    Range("A1:D1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False 'esp
    
    ' Re-enable alerts so Excel behaves normally for other tasks
    Application.DisplayAlerts = True
End Sub

Key Changes Explained #

• Application.DisplayAlerts = False: This turns off all Excel pop-up prompts (including the overwrite confirmation) until we explicitly turn them back on.
• Application.DisplayAlerts = True: Always re-enable alerts after the critical operation—leaving them off could cause unexpected behavior later (like not warning you if you try to close an unsaved workbook).

Bonus: Cleaner, More Reliable Version #

Using Activate and Select can make macros slow and prone to errors if users switch windows while the macro runs. A better approach is to use object variables to reference your workbooks and worksheets directly:

Sub AV_Improved()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    
    ' Disable alerts temporarily
    Application.DisplayAlerts = False
    
    ' Set references to your files and sheets (no activation needed)
    Set sourceWB = ThisWorkbook ' The workbook running this macro
    Set sourceWS = sourceWB.Sheets("AV")
    Set targetWB = Workbooks.Open(Filename:="T:\Cleveland\Avon\Monthly Sales\Monthly Sales 2018.xls")
    Set targetWS = targetWB.Sheets("Avon")
    
    ' Copy and paste values/number formats directly
    sourceWS.Range("A1:AC88").Copy
    targetWS.Range("A1:D1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Application.CutCopyMode = False
    
    ' Re-enable alerts
    Application.DisplayAlerts = True
End Sub

This version is faster, more readable, and less likely to break if you rearrange your workbook windows.

内容的提问来源于stack exchange，提问作者David