VBA SaveAs, possibly a tmp file issue

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
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

Code:
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Your code doesn't compile.

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

Code:
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
 
Upvote 0
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.
 
Upvote 0
shg said:
Your code doesn't compile.

bsquad said:
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.
 
Upvote 0
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
Code:
Application.EnableEvents = False
included, when I opened the new import file, and because I had
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,822
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top