VBA - Remain on 'Active Page'

[Peter Muller]

Good afternoon,

I have a macro that does multiple tasks on different sheets in my workbook.

I have the same macro button on each sheet. When I run the macro, it does what I want it to, but my cursor does not remain on the active sheet, but jumps to a different sheet.


I am looking for code to keep the cursor on the active sheet and not jump to another.

I tried various options, but none works, e.g.,

With ActiveSheet

Range("A1").Select

End With


Please help

 

[richh]

Hi Peter,

At the start of your code, store the worksheet that you want to remain on. Then perform your code and at the end, activate the stored worksheet and then append your example code to the bottom.

Sub blahblah()
    Dim ws  As Worksheet
    
    Set ws = Application.ThisWorkbook.ActiveSheet
    
    'Your Code Goes Here
    'Call ReleaseTheKracken()
    'etc.
    
    ws.Activate
    
    With ws
        .Cells(1, 1).Select
    End With
End Sub

 

[Peter Muller]

Hi Peter,

At the start of your code, store the worksheet that you want to remain on. Then perform your code and at the end, activate the stored worksheet and then append your example code to the bottom.

Sub blahblah()
    Dim ws  As Worksheet
   
    Set ws = Application.ThisWorkbook.ActiveSheet
   
    'Your Code Goes Here
    'Call ReleaseTheKracken()
    'etc.
   
    ws.Activate
   
    With ws
        .Cells(1, 1).Select
    End With
End Sub

Thank you for helping. I run the same macro from different worksheets, and want to remain on the active worksheet and not jump to the stored worksheet.

 

[Fluff]

Can you post your code.

 

[Peter Muller]

Can you post your code.

Okay, will do first thing in the morning

 

[Fluff]

Ok

 

[Peter Muller]

Sub Protect_Sheets()
   
    With Sheets("Reports")
        .Unprotect "Password"
        .Columns("K:AH").EntireColumn.Hidden = True
        .Protect "Password"
    End With
   
    With Sheets("Data")
        .Unprotect "Password"
        .Columns("A:O").EntireColumn.Hidden = True
        .Visible = False
        .Protect "Password"
    End With
   
    Sheets("Procedure").Visible = False
   
    With Sheets("Daily")
        .Unprotect "Password"
        .Columns("H").EntireColumn.Hidden = True
        .Columns("J").EntireColumn.Hidden = True
        .Protect "Password"
    End With
   
    Sheets("Stats").Protect "Password"
   
   
    'I have a number of additional sheets, where i use the same "Protect" & "Unprotect" macro's, so the same button is on each of the 20 odd sheets.
    'When i activate the macro, the sheet that i am running the macro from must remain visible - the cursor must not jump to "Reports"
   
    'With Active Sheet
    'Protect Sheet
    'Remain on the active sheet, etc.
       
    'Then i also need code to protect all the sheets to the right of "reports from a '2nd' protect macro button,_
    'and the cursor must remain on the sheet where i am running the macro from, if possible please.
       
End Sub

 

[Fluff]

There is nothing in that code which would change the sheet you are on initially, assuming you are not running it from the Procedure sheet

 

[Peter Muller]

There is nothing in that code which would change the sheet you are on initially, assuming you are not running it from the Procedure sheet

The sheet definitely changes to "Reports", irrespective of which sheet i am running the macro from.

 

[Fluff]

Try stepping through the code line by line using F8 to see where it changes sheets.