Why does my VBA code jump to ActiveWorkbook.Save without running the rest of the code?

[statiCat]

I have a long macro that works great every time I run it except if I try to save at the end. If I put ActiveWorkbook.Save at the end it skips the code in front of it and just saves and does nothing else. What would cause this? I'm using the latest Excel and windows 10.

 

[My Aswer Is This]

We need to see your code.

 

[statiCat]

I've cut out lines in the middle because it's too long, but you can see that regardless of whether today is greater or less than 7 days from the last date the data will refresh. The problem is it's not refreshing, it's jumping straight to save and then it does nothing. Without ActiveWorkbook.Save everything works fine.

 Sub Workbook_Refresher()
   
    ActiveWorkbook.Sheets("FirstSheet").Activate
    Dim lastRowDate As Date
    lastRowDate = ActiveSheet.Range("B458").<wbr>Value
    
    If Date - lastRowDate >= 7 Then
        Dim ws As Worksheet
        For Each ws In Worksheets
            Select Case ws.Name
                Case " FirstSheet ", "ThirdSheet”                
                    Application.GoTo ws.Rows("459:459") 'select last row'
                    Selection.Delete
                    Application.GoTo ws.Range("M458")
                    Selection.Formula = "=L458/C458"
                                       
                Case "Sheet2Graph", “Sheet4Graph”
                    
                    Application.GoTo ws.Rows("4:4") 'select top row
                    Selection.Delete
                    Application.GoTo ws.Rows("59:59") 'select bottom row
                    Selection.Delete
    
            End Select
            
            Next ws
 
    For Each oWksht In ActiveWorkbook.Worksheets
        For Each oChart In oWksht.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                mySrs.Formula = WorksheetFunction.Substitute(<wbr>mySrs.Formula, 57, 58)
            Next
        Next
    Next
            
    End If
 
    For Each oWksht In ActiveWorkbook.Worksheets
        For Each oChart In oWksht.ChartObjects
            For Each mySrs In oChart.Chart.SeriesCollection
                mySrs.Formula = WorksheetFunction.Substitute(<wbr>mySrs.Formula, 58, 58)
            Next
        Next
    Next

ActiveWorkbook.RefreshAll
ActiveWorkbook.Sheets("Sheet2Graph”) Activate
Application.DisplayAlerts = False

ActiveWorkbook.Save
    
End Sub

 

[JonXL]

Does the refresh line fire when you step through the procedure?

 

[statiCat]

Yes, when I step through it does fire.

However, if I try to run the macro by double clicking its name in the list of macros it does not fire.

 

[Fluff]

You seem to have a mix of quotes, they should all be ", but you also have “ & ”

 

[statiCat]

You seem to have a mix of quotes, they should all be ", but you also have “ & ”

Where are you looking? My code works fine except for the saving part.

 

[Fluff]

The 3 part in red

Case " FirstSheet ", "ThirdSheet[COLOR=#ff0000]” [/COLOR]               
                    Application.GoTo ws.Rows("459:459") 'select last row'
                    Selection.Delete
                    Application.GoTo ws.Range("M458")
                    Selection.Formula = "=L458/C458"
                                       
                Case "Sheet2Graph", [COLOR=#ff0000]“[/COLOR]Sheet4Graph[COLOR=#ff0000]”[/COLOR]

There maybe others.
Also this

My code works fine except for the saving part.

is not what you said here

The problem is it's not refreshing,

 

[statiCat]

The 3 part in red

Case " FirstSheet ", "ThirdSheet[COLOR=#ff0000]” [/COLOR]               
                    Application.GoTo ws.Rows("459:459") 'select last row'
                    Selection.Delete
                    Application.GoTo ws.Range("M458")
                    Selection.Formula = "=L458/C458"
                                       
                Case "Sheet2Graph", [COLOR=#ff0000]“[/COLOR]Sheet4Graph[COLOR=#ff0000]”[/COLOR]

There maybe others.
Also this is not what you said here

I changed the names of the sheets for privacy reasons, so the quote marks might be inconsistent in this thread I guess. The code works fine except when I add the ActiveWorkbook.Save part, then it is not refreshing the data, which IS what I said here:

The problem is it's not refreshing, it's jumping straight to save and then it does nothing. Without ActiveWorkbook.Save everything works fine.

 

[rlv01]

If you are using the 'bad' double quote characters then it is unlikely your code is running fine. For example used in this line

[COLOR=#574123]      Case "Sheet2Graph", “Sheet4Graph”
[/COLOR]

it means that “Sheet4Graph” is not being evaluate as a literal string, but instead as an empty variable. Let the compiler help you weed them out by putting this statement at the top of your code module

Option Explicit

Which will force all variables to be defined (which will help your coding in other ways as well)