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 ?

 

[Akuini]

I start in one of the Sheets 1-12, and run Macro 1 with a button click. ... and then I'm trying (as part of Macro 2, at the end) to return to the very original sheet (one of 1-12).

Does it mean you want to go back to the sheet where you run macro1?
So, probably something simpler:

Public sName As String

Sub macro1()
'at the beginning of code before it goes to another sheet
sName = ActiveSheet.Name

End Sub


Sub macro2()

'at the end of macro2
If sName <> "" Then Sheets(sName).Activate
End Sub

 

[Dataframe]

Sounds good.

Hi Alex. Just letting you know that the above code didn't work. But as I was going through it, I was thinking of options, and am wondering if I can mark a particular sheet with a reference that I can use to return to. For example, when I start Macro 1, giving that sheet a designation such as ThisSheet or StartSheet or ReturnSheet (or something) that I can use in the code at the end of Macro 2 that takes me back to it. Thoughts ?

 

[Dataframe]

Does it mean you want to go back to the sheet where you run macro1?
So, probably something simpler:

Public sName As String

Sub macro1()
'at the beginning of code before it goes to another sheet
sName = ActiveSheet.Name

End Sub


Sub macro2()

'at the end of macro2
If sName <> "" Then Sheets(sName).Activate
End Sub

Hi Akuini. Just letting you know that the above code didn't work. But as I was going through it, I was thinking of options, and am wondering if I can mark a particular sheet with a reference that I can use to return to. For example, when I start Macro 1, giving that sheet a designation such as ThisSheet or StartSheet or ReturnSheet (or something) that I can use in the code at the end of Macro 2 that takes me back to it. Thoughts ?

 

[Akuini]

Hi Akuini. Just letting you know that the above code didn't work.

Could you explain what happened? did the code activate a wrong sheet?

 

[Dataframe]

Could you explain what happened? did the code activate a wrong sheet?

Yes, it did. This part of the code at the end of Macro 2 had no effect ... If sName <> "" Then Sheets(sName).Activate ... Macro 2 performed exactly the same when I deleted that code. The last function that Macro 2 performs is to hide the sheet I'm in at that time, so it simply takes me to the sheet to the right of it, rather than returning me to the sheet I want to get to (which is the sheet Macro 1 is run from).

 

[Akuini]

Macro 2 performs is to hide the sheet I'm in at that time

Do you mean "the sheet Macro 1 is run from" is now hidden?
You can't activate a hidden sheet, you need to unhide it first.

Sub macro2()

'at the end of macro2
If sName <> "" Then
Sheets(sName).Visible = True
Sheets(sName).Activate
End If
End Sub

 

[Dataframe]

Do you mean "the sheet Macro 1 is run from" is now hidden?
You can't activate a hidden sheet, you need to unhide it first.

Sub macro2()

'at the end of macro2
If sName <> "" Then
Sheets(sName).Visible = True
Sheets(sName).Activate
End If
End Sub

No, I mean the sheet Macro 2 is run from is hidden. The last function that Macro 2 performs is to hide the sheet that it's run from.

 

[Akuini]

No, I mean the sheet Macro 2 is run from is hidden. The last function that Macro 2 performs is to hide the sheet that it's run from.

Hm, try checking if the sheet name is correct (via message box):

Sub macro2()

'at the end of macro2
MsgBox "Sheet: " & sName
If sName <> "" Then
Sheets(sName).Activate
End If
End Sub

 

[Dataframe]

Hm, try checking if the sheet name is correct (via message box):

Sub macro2()

'at the end of macro2
MsgBox "Sheet: " & sName
If sName <> "" Then
Sheets(sName).Activate
End If
End Sub

The sheet name was nothing. The message box said : "Sheet:"

 

[Akuini]

The sheet name was nothing. The message box said : "Sheet:"

That's strange. What about in macro1:

Sub macro1()
'at the beginning of code before it goes to another sheet
sName = ActiveSheet.Name
MsgBox "Sheet: " & sName
End Sub