VBA SaveAs, possibly a tmp file issue

[bsquad]

I am having an issue with Excel 2013 crashing where I have a master file that generates a new workbook from selected sheets from the master file.
the scenario is I create the new workbook with the selected or grouped sheets from the master file, it saves and closes the new workbook automatically. Now if I open the newly generated workbook, close it, and try and generate another one from the master file, Excel crashes.

Basically put, I can't run the code below, open the newly generated excel file, close it, run the code again.
If I run the code, open the newly generated excel file, close the newly generated excel file, close the master file, reopen the master file, then it doesn't crash >> this is what leads me to believe it has to do something with a tmp file.

parts of this code you can ignore as the initial part I am selecting or "grouping" the sheets based on tab color

Sub v2()
    Dim iWorkbook, eWorkbook As Workbook
        Set iWorkbook = ThisWorkbook
    Dim iSheet As Worksheet
    Dim iMark As Boolean
    Dim x, i As Integer
    Dim iCarrier, cell As Range
        Set iCarrier = ThisWorkbook.Worksheets("Instructions").Range("C61:C65")
Application.AskToUpdateLinks = False: Application.DisplayAlerts = False: Application.ScreenUpdating = False: Application.DisplayStatusBar = True: Application.EnableEvents = False
    For Each cell In iCarrier
        DoEvents
            Application.StatusBar = cell.Value
        If cell.Value <> "" Then
            For i = 1 To iWorkbook.Worksheets.Count
                If iWorkbook.Worksheets(i).Visible Then
                    If iWorkbook.Worksheets(i).Tab.Color = RGB(99, 102, 106) Or iWorkbook.Worksheets(i).Tab.Color = RGB(0, 160, 210) Or iWorkbook.Worksheets(i).Tab.Color = RGB(112, 32, 130) _
                    Or iWorkbook.Worksheets(i).Tab.Color = RGB(234, 199, 241) Or iWorkbook.Worksheets(i).Tab.Color = RGB(213, 142, 227) Or iWorkbook.Worksheets(i).Tab.Color = RGB(84, 24, 97) _
                    Or iWorkbook.Worksheets(i).Tab.Color = RGB(56, 16, 65) Or iWorkbook.Worksheets(i).Tab.Color = RGB(0, 112, 192) Or iWorkbook.Worksheets(i).Tab.Color = RGB(0, 32, 96) Then
                        Worksheets(i).Select Not iMark
                        iMark = True
                    End If
                End If
            Next
                ActiveWindow.SelectedSheets.Copy
                Set eWorkbook = ActiveWorkbook

                eWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & cell.Value & "_Import.xlsx", FileFormat:=xlOpenXMLWorkbook
                eWorkbook.Close
end sub

 

[shg]

Your code doesn't compile.

Also, it appears that you save the same workbook (repeatedly created) by several names.

 

[shg]

This compiles ...

Sub bsquad()
  Dim cell          As Range
  Dim wks           As Worksheet
  Dim iMark         As Boolean

  With Application
    .AskToUpdateLinks = False
    .DisplayAlerts = False
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .EnableEvents = False

    For Each cell In ThisWorkbook.Worksheets("Instructions").Range("C61:C65").Cells
      .StatusBar = cell.Value
      
      If Len(cell.Text) Then
        For Each wks In ThisWorkbook.Worksheets
          If wks.Visible = xlSheetVisible Then
            Select Case wks.Tab.Color
              Case RGB(99, 102, 106), RGB(0, 160, 210), RGB(112, 32, 130), _
                   RGB(234, 199, 241), RGB(213, 142, 227), RGB(84, 24, 97), _
                   RGB(56, 16, 65), RGB(0, 112, 192), RGB(0, 32, 96)
                wks.Select Not iMark
                iMark = True
            End Select
          End If
        Next wks
      End If
    Next cell

    ActiveWindow.SelectedSheets.Copy
    With ActiveWorkbook
      .SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & cell.Value & "_Import.xlsx", _
              FileFormat:=xlOpenXMLWorkbook
      .Close
    End With

    .ScreenUpdating = True
    .EnableEvents = True
  End With
End Sub

 

[bsquad]

thank you for the response. Yes, as far as it saving multiple workbooks; that was the intention, from Range("C61:C65") the user can input different names for the files. Could you explain a little more by what you mean when you say 'Compile' ; the code you had provided looks similar and am unable to differentiate it on a technical basis - if that makes sense.

 

[shg]

Your code doesn't compile.

Could you explain a little more by what you mean when you say 'Compile'

Copy the code from your post into a module and press Debug > Compile VBA Project.

 

[bsquad]

As I appreciate the response again, I was able to figure it out - it was an underlying issue with a Worksheet_Change(ByVal ...) issue on the same tab ('Instructions'). Although I had

Application.EnableEvents = False

included, when I opened the new import file, and because I had

Application.EnableEvents = True

at the end of my code, it triggered something with that sheet ('Instructions') - resolution was to move the Worksheet_Change code to a separate sheet. >> I was reading somewhere that a Worksheet_Change that hides rows can interfere with the tmp file for some reason.

 

[shg]

Glad you got it sorted.