CODEWITHSUNDEEP
excelvba
David
David

Reputation: 1

Overwrite data without asking

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

Upvotes: 0

Views: 1157

Answers (1)

Xabier
Xabier

Reputation: 7735

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

Upvotes: 1

Related Questions