Hi all,

I've been experiencing this one problem with a file which pulls worksheets in from another file via macro, here's the weird thing:

If I open the file and make some modifications then save it, it's fine.
If I open the file and run a particular macro, then try and save, it crashes (Excel is not responding, closing program etc.)

It crashes whatever kind of saving I try to do. Save As, Autorecover etc...

The macro basically takes an external workbook and copies a particular sheet from the external workbook to this workbook.

Does Excel have some strange constraint when it comes to adding/deleting worksheets via VBA?

Here's the snippet of code that seems to be causing the problem:
c.Offset(0,1) is the path to the external workbook
c.Offset(0,2) is the worksheet name it should pull
c.Offset(0,3) is the new name it should assign the copied worksheet in the current workbook.

Sub PullFromOtherSheets()

Dim wb As Workbook
Dim newwb As Workbook
Dim ws As Worksheet
Dim newws As Worksheet
Dim oldws As Worksheet

Dim c As Range
Dim trackers As Range

Set wb = ThisWorkbook
Set ws = wb.Sheets("Settings")

Dim endcell As Integer
'last row in range
endcell = ws.Cells(2, 1).End(xlDown).Row

Dim x As Integer

For x = 2 To endcell
    Set c = ws.Cells(x, 1)
    
    Application.StatusBar = "Pulling from " & c.Offset(0, 1).Value
    
    Set newwb = Workbooks.Open(c.Offset(0, 1).Value, False, True)
    Set newws = newwb.Sheets(c.Offset(0, 2).Value)
    
    On Error Resume Next
    'In case sheet doesn't exist
    Set oldws = wb.Sheets(c.Offset(0, 3).Value)
    On Error GoTo 0
    
    'Application.DisplayAlerts = False
    
    If Not oldws Is Nothing Then oldws.Delete
    
    newwb.Worksheets(newws.name).Copy wb.Worksheets(ws.name)
    
    'Application.DisplayAlerts = True
    
    Set oldws = wb.Sheets(newws.name)
    oldws.name = c.Offset(0, 3).Value
    oldws.Visible = xlSheetHidden
    
    newwb.Close SaveChanges:=False
Next x

Application.StatusBar = "Ready"


End Sub
Thanks and regards
Duncan