VBA Macro crashing Excel.

[gmittar]

Hi All,

I've recorded a macro to copy a sheet and save it as a new file, with a few modifications (copies values over a pivot table, formats a few cells). The macro works well, when it works. About half the time, it just crashes excel, with no error message or anything. When it works, it works very quickly.

Is there something in the macro that tends to crash Excel, or is generally higher risk?

Sub Export_Verification()'
' Export_Verification Macro
'
    Application.ScreenUpdating = False
    Sheets("Criteria Verification").Select
    Sheets("Criteria Verification").Copy
    ActiveWorkbook.BreakLink Name:= _
        "Pathreplacedforprivacy" _
        , Type:=xlExcelLinks
    Range("L10:N25").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M10").Select
    Selection.Font.Bold = True
    Range("L12").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Range("M25").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("N25").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""-""??_);_(@_)"
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
    Range("l1:n2").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("a1").Select
        ActiveWorkbook.SaveAs Filename:= _
        Range("m1") & "" & Range("m2"), _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.ScreenUpdating = True
End Sub

 

[Yongle]

It would be helpful to know the line where it crashes and details of the message if any
- the line that jumps out is button deletion
- VBA may be encountering unexpected or missing values in M1 and M2

[COLOR=#000080][I]Code below deletes a button [/I][/COLOR]
   ActiveSheet.Shapes.Range(Array("Button 1")).Select
   Selection.Delete

[COLOR=#000080][I]One way to avoid a crash if button is not present:[/I][/COLOR]
   On Error Resume Next
   ActiveSheet.Shapes.Range(Array("Button 1")).Delete
   On Error GoTo 0

[COLOR=#000080][I]What is purpose of this line? Nothing is pasted[/I][/COLOR]
   Sheets("Criteria Verification").Copy

[COLOR=#000080][I](This is nothing to do with your crash) 
Avoid selecting ranges (often its unneceassry)
instead of:[/I][/COLOR]
   Range("L10:N25").Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
[COLOR=#000080][I]use:[/I][/COLOR]
   Range("L10:N25").Copy
   Selection.PasteSpecial Paste:=xlPasteValues
[COLOR=#000080][I]and avoid copy\paste values like this:[/I][/COLOR]
      Range("L10:N25").Value = Range("L10:N25").Value

 

[gmittar]

Yongle,
Thank you for taking the time to pore through my sloppy code. I've already changed some of it, but your notes are significant and helpful.

Unfortunately, when Excel crashes, it gives no error messages or notices. It just closes.

The intent of the macro is to copy the sheet into a new file, and save it to a specific drive, in a clean format. Hence, the button deletion. I'll take your note on that line.

The copy "criteria verification" sheet is the sheet that I'm extracting. That's just the name of the sheet.

The copy paste values is intended to replace a pivot table selection with the values. I tried your suggestion, and it ends up pasting the values two columns to the left, therefore not overwriting the pivot table. Is there a way to make sure it pastes in the same spot as the original values?

It would be helpful to know the line where it crashes and details of the message if any
- the line that jumps out is button deletion
- VBA may be encountering unexpected or missing values in M1 and M2

[COLOR=#000080][I]Code below deletes a button [/I][/COLOR]
   ActiveSheet.Shapes.Range(Array("Button 1")).Select
   Selection.Delete

[COLOR=#000080][I]One way to avoid a crash if button is not present:[/I][/COLOR]
   On Error Resume Next
   ActiveSheet.Shapes.Range(Array("Button 1")).Delete
   On Error GoTo 0

[COLOR=#000080][I]What is purpose of this line? Nothing is pasted[/I][/COLOR]
   Sheets("Criteria Verification").Copy

[COLOR=#000080][I](This is nothing to do with your crash) 
Avoid selecting ranges (often its unneceassry)
instead of:[/I][/COLOR]
   Range("L10:N25").Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
[COLOR=#000080][I]use:[/I][/COLOR]
   Range("L10:N25").Copy
   Selection.PasteSpecial Paste:=xlPasteValues
[COLOR=#000080][I]and avoid copy\paste values like this:[/I][/COLOR]
      Range("L10:N25").Value = Range("L10:N25").Value

 

[gmittar]

One of the items that I'm trying to clean up in this macro are the "select, then go to" lines. These lines are intended to format that former pivot table section by finding the end of the data.

The size of the pivot table varies depending on the filter selection, so I can't statically format certain lines. Is there a better way to find the last row of data, or is this maybe the best way.

example - finds the last row,of data to format as bold.

Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
        Selection.Font.Bold = True

 

[Yongle]

Pivot Tables:
Read and digest this helpful guide

 

[Yongle]

If the wrong workbook is active then the code would fail
- is the code in a DIFFERENT workbook to the one containing "Criteria Verification" ?
- there is no reference to a workbook in the code

To activate it, use something like this near the op of the code

Workbooks("workbookname.xlsx").Activate
Sheets("Criteria Verification").Activate

Will be back online tomorrow

 

[gmittar]

Pivot Tables:
Read and digest this helpful guide

Wow, that's a VERY useful reference. Thank you!