How to get back to source worksheet after macro runs

[lindseyschwab]

Hi, I have the below macro written. However, when it runs through it ends on the last tab of my workbook and not on the worksheet I ran the macro on. Is there a way to add in what worksheet to come back to once the macro runs?

Macro:
UnprotectRefreshAll()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="mypassword"
Next ws

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="mypassword", _
AllowUsingPivotTables:=True

Next

End Sub

 

[rlv01]

Something like this might work.

'Macro:
Sub UnprotectRefreshAll()
    Dim ws As Worksheet
    Dim SaveWS As Worksheet
    
    Set SaveWS = ActiveSheet
    
    On Error Resume Next
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="mypassword"
    Next ws
    
    ActiveWorkbook.RefreshAll
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
    Next
    
    SaveWS.Activate
End Sub

 

[lindseyschwab]

Something like this might work.

'Macro:
Sub UnprotectRefreshAll()
    Dim ws As Worksheet
    Dim SaveWS As Worksheet
   
    Set SaveWS = ActiveSheet
   
    On Error Resume Next
   
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="mypassword"
    Next ws
   
    ActiveWorkbook.RefreshAll
   
    For Each ws In ActiveWorkbook.Worksheets
        ws.Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
    Next
   
    SaveWS.Activate
End Sub

This didn't seem to work. I am not trying to have it save. I want my macro to run and then go back to the worksheet that I was originally on vs. the last worksheet in my workbook.

 

[johnnyL]

Try something like the following:

    Dim SourceSheet As Worksheet
    Set SourceSheet = ActiveSheet
'
' Code you want to run
'
    Application.Goto SourceSheet.Range("A1")                ' <--- Change the cell reference if you want to

 

[MARK858]

@lindseyschwab I can see nothing in your original code that changes the active sheet (and it doesn't for me), are you sure that is all your code?
and you have no other code in the workbook?

 

[Alex Blakenburg]

As @MARK858 pointed out there is nothing in the code you are showing that would cause the Activesheet to change to another sheet.

In case you are triggering an event macro with the refresh you could try adding the following around the refreh line or if you want to play it really safe put the first one after the Dim statements and the last befor the End Sub

    Application.EnableEvents = False
    ActiveWorkbook.RefreshAll
    Application.EnableEvents = True

 

[rlv01]

This didn't seem to work. I am not trying to have it save. I want my macro to run and then go back to the worksheet that I was originally on vs. the last worksheet in my workbook.

The 'SaveWS' variable only means we are saving the name of the active worksheet so we can return to it after the macro finishes running. You have error checking turned off and I wonder if your macro is encountering something that is terminating operation. Try this slight variation which will re-enable error checking before calling ActiveWorkbook.RefreshAll . You should get a pop-up message box saying "Macro Complete" after the macro finishes running if everything is normal.

'Macro:
Sub UnprotectRefreshAll()
    Dim ws As Worksheet
    Dim SaveWS As Worksheet
    
    Set SaveWS = ActiveSheet
    
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="mypassword"
    Next ws
    On Error GoTo 0
    
    ActiveWorkbook.RefreshAll
    
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        ws.Protect Password:="mypassword", _
        AllowUsingPivotTables:=True
    Next ws
    On Error GoTo 0
    
    SaveWS.Activate
    MsgBox "Macro Complete"
End Sub