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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
Your code doesn't compile.

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
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
 

bsquad

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
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.
 

bsquad

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
Glad you got it sorted.
 

Forum statistics

Threads
1,081,556
Messages
5,359,548
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top