Sometimes this macro runs but does not complete

[sjgross]

I have this macro that loops through the sheets in a workbook and uses Paste Special | Values to

1. paste the values
2. delete the button that ran the macro
3. save the workbook with a new name.

Sometimes the macro doesn't complete. It runs and I can see that the formulas have been converted to values, but the button hasn't been deleted and the workbook hasn't been saved with the new name. I don't see any error when the macro doesn't complete, and if I click the button again, the macro completes all 3 steps.

Here is the macro:

Sub Paste_Values()

Dim Sht As Worksheet
Dim ThisSheet As Worksheet
Dim ws As Worksheet

Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet

'Loop through all cells
For Each Sht In ThisWorkbook.Worksheets
With Sht
.Activate
End With
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next Sht
'End of loop
'Delete the Store Sheet if it exists
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Store" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Next ws

'Application.ScreenUpdating = True

ThisSheet.Select
'Finish on the sheet we started
Set ThisSheet = Nothing
'Delete the button on the workbook copy
Range("A1").Select
ActiveSheet.Shapes("Button 1").Select
Selection.Cut
Application.ScreenUpdating = True
'Save the workbook
MyName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="Values_" & MyName


End Sub

I'm not sure how to fix this. Any help would be much appreciated!

Susan Gross

 

[Andrew Poulsom]

Try removing Exit Sub in (or change it to Exit For):

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Store" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
            Exit Sub
        End If
    Next ws

The macro exits when sheet Store exists. Second time around, when it doesn't exist, the macro continues.

 

[RoryA]

If you have a sheet called "Store" your code deletes it and then terminates. Try this version:

Sub Paste_Values()
   Dim Sht As Worksheet
   Dim ThisSheet As Worksheet
   Dim ws As Worksheet
   
   Application.ScreenUpdating = False
   Set ThisSheet = ActiveSheet
   
   'Loop through all cells
   For Each Sht In ThisWorkbook.Worksheets
      With Sht.UsedRange
         .Copy
         .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                              SkipBlanks:=False, Transpose:=False
      End With
   Next Sht
   'End of loop
   'Delete the Store Sheet if it exists
   For Each ws In ThisWorkbook.Worksheets
      If ws.Name = "Store" Then
         Application.DisplayAlerts = False
         ws.Delete
         Application.DisplayAlerts = True
         Exit For
      End If
   Next ws
   
   'Application.ScreenUpdating = True
   
   ThisSheet.Select
   'Finish on the sheet we started
   Set ThisSheet = Nothing
   'Delete the button on the workbook copy
   ActiveSheet.Shapes("Button 1").Delete
   
   Application.ScreenUpdating = True
   'Save the workbook
   MyName = ActiveWorkbook.Name
   ActiveWorkbook.SaveAs Filename:="Values_" & MyName
End Sub

 

[dave3009]

Oh well, here's my tu'pence worth, hope it helps

Sub Paste_Values()
Dim Sht As Worksheet
Dim ThisSheet As Worksheet
Dim ws As Worksheet

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
Set ThisSheet = ActiveSheet

For Each Sht In ThisWorkbook.Worksheets
    Select Case Sht.Name
        Case "Store": Sht.Delete
        Case Else:
            With Sht
                .Activate
                .Cells.Copy
                .Range("A1").PasteSpecial xlPasteValues
            End With
    End Select
Next Sht
ThisSheet.Activate
ActiveSheet.Shapes("Button 1").Cut
Set ThisSheet = Nothing
MyName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="Values_" & MyName

    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

 

[sjgross]

Andrew,

Thank you very much for your quick reply. I tested your suggestion, and it works wonderfully.

I appreciate the help!

Susan Gross

 

[sjgross]

Rory and Dave,

Thank you as well for your suggestions!

-Susan