CODEWITHSUNDEEP
excelvba
DearDeer
DearDeer

Reputation: 193

ActiveWorkbook.Close doesn't work properly in Excel 365

I have a main excel file with macro which copies data to a newly created file. When I run the below part of code in Excel 2016 everything works correctly - new workbook closes and "Completed" message is displayed. But when I run it in Excel 365, csv is successfully exported, but the main file closes instead of the newly created file and I never see the "Completed" message.

I use both Excel files remotely, so there might be delay involved.

 ' copy table to a new sheet and export to csv

 tbl.Range.Copy
 Workbooks.Add
 ActiveSheet.Paste
  Application.DisplayAlerts = False
 ActiveWorkbook.SaveAs Filename:= _
 fcsv _
 , FileFormat:=xlCSV, CreateBackup:=False

 ActiveWorkbook.Close
 Application.DisplayAlerts = True
 MsgBox "Completed"

Upvotes: 0

Views: 364

Answers (1)

BigBen
BigBen

Reputation: 50008

Use a workbook variable and avoid ActiveWorkbook (or ActiveSheet).

Dim wb As Workbook
Set wb = Workbooks.Add

Dim ws As Worksheet
Set ws = wb.Worksheets(1)

tbl.Range.Copy ws.Range("A1") '<~ copy/paste in one line

Application.DisplayAlerts = False

wb.SaveAs Filename:= _
 fcsv _
 , FileFormat:=xlCSV, CreateBackup:=False
wb.Close SaveChanges:=False

Application.DisplayAlerts = True

MsgBox "Completed"

Upvotes: 2

Related Questions