VBA Code for 2nd Previously Active Sheet

[Dataframe]

Hi everyone. Is there VBA code to take you to the 2nd Previously Active Sheet ?

I have Sheets 1 to 12, a Raw Data sheet, and a Transfer sheet. From one of the sheets 1-12 (continually varying) I run a macro that processes and moves selected data from the Raw Data sheet to the Transfer sheet.

After that is done, I need to return to whichever of the Sheets 1 to 12 that I originally came from.

So, to recap ...
1. Start in one of the sheets 1-12. Running a Macro there takes me to ...
2. Raw Data sheet. The Macro processes data and transfers it to ...
3. Transfer sheet. I then wish to return to whichever sheet I started in.

Can this be done ?

 

[Alex Blakenburg]

Do you just mean something like this ?

Sub ReturnToCurrentSheet()
    Dim CurrentSheet As Worksheet
    
    Set CurrentSheet = ActiveSheet
    
    ' Run your macro here
    
    ' Return to original sheet
    CurrentSheet.Select

End Sub

Although I haven't seen your code, you can normally write it in such a way that you never actually leave the sheet you were on in the first place. In fact in writing your code it preferable to not use Select & Activate since they make the code hard to follow and slow down its performance.

 

[Dataframe]

Hi Alex. I think your code will return me to the previously active sheet, yes? I need to return to the SECOND previously active sheet, not the previously active sheet.

 

[Alex Blakenburg]

How are you calling the macro ? What sheet are you on when you call the macro ?
How is the macro supposed to know what the sheet before that was ?

 

[Dataframe]

How are you calling the macro ? What sheet are you on when you call the macro ?
How is the macro supposed to know what the sheet before that was ?

I start in one of the Sheets 1-12, and run Macro 1 with a button click.
Then I'm in the sheet "Raw Data" (as part of Macro 1 running), where data is filtered and copied (as part of Macro 1 running).
Then I'm in the sheet "Transfer" (as part of Macro 1 running), where data is pasted, and Macro 1 stops.

Then in the sheet "Transfer", I view the data, and sometimes email that one sheet. That sheet contains Macro 2 which is to clear (delete) the data, and then I'm trying (as part of Macro 2, at the end) to return to the very original sheet (one of 1-12).

 

[Alex Blakenburg]

See if something like this works for you.

In the Module > ThisWorkbook - Copy the code below
Change the 2 sheet names to your sheet names for Raw and Transfer

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' XXX Change the Sheet names of the Sheets you do not want to Store XXX
    If Sh.Name <> "Raw" And Sh.Name <> "Transfer" Then
        Set ReturnToSht = Sh
    End If
End Sub

In a standard module declare the Public Variable below and then at the end of you Macro 2 add the line to go back to Public Variable Sheet.

Public ReturnToSht As Worksheet

Sub DataFramesFirstMacro()
    ' Do Stuff the below is just a test example
    Worksheets("Raw").Select
    Worksheets("Transfer").Select
End Sub

Sub DataFramesSecondMacro()
    ' Do more stuff
    ReturnToSht.Select

End Sub

 

[Dataframe]

See if something like this works for you.

In the Module > ThisWorkbook - Copy the code below
Change the 2 sheet names to your sheet names for Raw and Transfer

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' XXX Change the Sheet names of the Sheets you do not want to Store XXX
    If Sh.Name <> "Raw" And Sh.Name <> "Transfer" Then
        Set ReturnToSht = Sh
    End If
End Sub

In a standard module declare the Public Variable below and then at the end of you Macro 2 add the line to go back to Public Variable Sheet.

Public ReturnToSht As Worksheet

Sub DataFramesFirstMacro()
    ' Do Stuff the below is just a test example
    Worksheets("Raw").Select
    Worksheets("Transfer").Select
End Sub

Sub DataFramesSecondMacro()
    ' Do more stuff
    ReturnToSht.Select

End Sub

Hi Alex, I don’t completely understand, but I’m curious about the ReturnToSht.Select part … Can I make that variable? Meaning, the sheet it returns to varies?

 

[Alex Blakenburg]

The code in ThisWorkbook will make it variable. This will run every time you switch sheets.
(if your macro is disabling events we might have to change it from SheetDeactivate to SheetActivate)

 

[Dataframe]

The code in ThisWorkbook will make it variable. This will run every time you switch sheets.
(if your macro is disabling events we might have to change it from SheetDeactivate to SheetActivate)

Okay, I’ll try and see how it goes. Thanks, and I’ll reply again after I’ve tried it.

 

[Alex Blakenburg]

Sounds good.