Overwrite data without asking

Question

I want to edit this macro to overwrite previous data in another workbook without prompting. See attached code. Any help would be greatly appreciated.

Sub AV()

    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

End Sub

Perhaps Application.DisplayAlerts = False at the beginning of the sub (setting it to True at the end) would work. — John Coleman
– John Coleman, Commented Jan 8, 2018 at 13:39

Xabier · Accepted Answer · 2018-01-08 14:59:58Z

1

As per comments from John, and after tidying up your code a little, I believe something like the code below would do what you are expecting:

Sub AV()
Application.DisplayAlerts = False

Workbooks.Open Filename:="T:\Cleveland\Avon\Monthly Sales\Monthly Sales 2018.xls"
Workbook("Sales_By_Day_Location Analysis.xlsm").Worksheets("AV").Range("A1:AC88").Copy
Workbook("Monthly Sales 2018.xls").Sheets("Avon").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False 'esp
Application.DisplayAlerts = True
End Sub

You should attempt not to use Activate or Select statements. Also your copy range is a lot bigger than your paste range, so for the purpose of this answer I've changed the paste range to A1.

If you want to then save the workbooks and close it without a prompt, you could do something like:

Workbook("Monthly Sales 2018.xls").Close SaveChanges:=True

edited Jan 8, 2018 at 14:59

answered Jan 8, 2018 at 14:12

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Overwrite data without asking

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related