VBA code always worked but in excel 2019 crashes excel

[Mr Kulle]

Hello,

at work they updated excel to 2019 version and now my distribution macro crashes excel.
when following the macro step for step, it looks like the new workbook to copy 1 sheet to doesn't have any sheets

'''''''''''''''''''''' WRITE TO A NETWORK DRIVE ''''''
'''''''''''Dim paths  for filename to cells'''''''''
Sheets("Distribution").Select

Dim Path As String
Dim filename As String
Path = ActiveSheet.Range("E2")
filename = ActiveSheet.Range("D2")

Dim wb As Workbook
  Sheets("KPI").Copy
  Set wb = ActiveWorkbook
      With wb
     Range("A1").Select
ActiveCell.End(xlDown).Offset(2, 0).Select
ActiveCell.End(xlDown).Offset(2, 0).Select
ActiveCell.End(xlDown).Offset(2, 0).Select
Rows(ActiveCell.Row & ":" & Rows.Count).Delete
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
Application.DisplayAlerts = True
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
  End With

.

 

[Fluff]

Do you still want to save the workbook as an xls, or do you want to save it as either an xlsx, or xlsm?

 

[Mr Kulle]

either xls or xlsx is ok,

 

[Michael M]

Does the path string include the backslashes

Path = ActiveSheet.Range("E2")

 

[Mr Kulle]

i have 2 different server locations,

example :

W:\SHIP OUT\KPI\USERNAME\KPI\

\\ach-fs-02\pub_lmd$\Receiving instructions\WH Team\USERNAME\KPI\

 

[Fluff]

How about

ActiveWorkbook.SaveAs Path & filename & ".xlsx", 51

 

[Mr Kulle]

The problem in office 2019 lays within :
Sheets("KPI").Copy

Which should open a new work book and copy the sheet in it.
It does open a new workbook but somehow it is bugged and doesn't show any sheets or grid.
Therefor the actions after this code will cause excel to crash.

I reprogrammed it to below and now it works again :

'''''''''''''''''''''' WRITE TO A NETWORK DRIVE ''''''
'''''''''''Dim paths for filename to cells'''''''''
Sheets("Distribution").Select

Dim Path As String
Dim filename As String
Path = ActiveSheet.Range("E2")
filename = ActiveSheet.Range("D2")
Dim wb As Workbook

'NEW CODE
Sheets("KPI").Select
Range("A1:Q42").Select
Selection.Copy
Set wb = Workbooks.Add

With wb
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveCell.End(xlDown).Offset(2, 0).Select
ActiveCell.End(xlDown).Offset(2, 0).Select
ActiveCell.End(xlDown).Offset(2, 0).Select
Rows(ActiveCell.Row & ":" & Rows.Count).Delete
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
Application.DisplayAlerts = True
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
End With

End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback.