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 ?

 

[Dataframe]

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

The Same ... "Sheet:"

 

[Akuini]

The Same ... "Sheet:"

Not sure what happened.
Sorry, I'm running out of ideas, hope someone else could help.

 

[Dataframe]

Not sure what happened.
Sorry, I'm running out of ideas, hope someone else could help.

Okay, thanks for your help anyway. Cheers.

 

[Alex Blakenburg]

Focussing on Akuini's code.
1) How are you managing to call the same Macro from sheets 1-12 ?
Do you have a button on each sheet and do all the buttons link to the same macro1 by name ?
Or do you have something on the Quick Access Toolbar (QAT) ?

2) Is there a Macro that calls your Macro 1 or is it called directly from the Button or QAT ?

3) Do you have your Macro1 & Macro2 in a standard module under the Modules Node as shown below and do you have the Public sName right at the top as shown below.
4) Do you have any other references to sName other than the ones you see here.

 

[Alex Blakenburg]

Also please change the first MsgBox to MsgBox "Macro 1 Sheet: " & sName. You can change the other one as well but as long as we change at least one so we know which message box you are displaying.
Macro2 is dependant on Macro1 having been run first and run from Sheets 1-12.

 

[Dataframe]

Hi

Focussing on Akuini's code.
1) How are you managing to call the same Macro from sheets 1-12 ?
Do you have a button on each sheet and do all the buttons link to the same macro1 by name ?
Or do you have something on the Quick Access Toolbar (QAT) ?

2) Is there a Macro that calls your Macro 1 or is it called directly from the Button or QAT ?

3) Do you have your Macro1 & Macro2 in a standard module under the Modules Node as shown below and do you have the Public sName right at the top as shown below.
4) Do you have any other references to sName other than the ones you see here.

View attachment 80560

Hi Alex.

(Q1) All sheets 1-12 have the same Macro button in them.
(Q2) The button in any of those 12 sheets calls Macro 1.
(Q3) Yes, the Macros are in a standard module. I created the Macros by the Record Macro function.
(Q4) No, no other sName references.

Because Macro 2 is being run from a different sheet, that appears to be the problem.

SHEET A : Sheets 1-12 (Macro 1 starts ...
SHEET B : Raw Data sheet ( ... Macro 1 does stuff and returns to one of Sheet 1-12)
SHEET C : Transfer sheet (I view the data, get the info needed, possibly email this sheet, then delete its data and hide the sheet. The delete data and hide is Macro 2 button)

And after that it fails. At the end of Macro 2, I'm trying to follow the delete data and hide with going back to whichever of Sheets 1-12 I came from.

 

[Alex Blakenburg]

Thanks for addressing each point. The only one I am missing is do you have the "Public sName as String" right at the top as shown in my image ?
I as assuming that you are trying to return to SHEET A and not the the Sheet 1-12 that Macro 1 returns to SHEET B : Raw Data sheet ( ... Macro 1 does stuff and returns to one of Sheet 1-12)

Did you change the 2 message boxes to identify them and what appeared in the msgboxes when you ran Macro 1 (from a button on Sheet 1-12) and then Macro 2 in sequentially in order ?

 

[Dataframe]

Thanks for addressing each point. The only one I am missing is do you have the "Public sName as String" right at the top as shown in my image ?
I as assuming that you are trying to return to SHEET A and not the the Sheet 1-12 that Macro 1 returns to SHEET B : Raw Data sheet ( ... Macro 1 does stuff and returns to one of Sheet 1-12)

Did you change the 2 message boxes to identify them and what appeared in the msgboxes when you ran Macro 1 (from a button on Sheet 1-12) and then Macro 2 in sequentially in order ?

Sheet A is Sheets 1-12. I was just using Sheet A, Sheet B, Sheet C to refer to the three stages of the process. In other words, Sheet A is any one of 12 possible sheets, Sheet B is one set sheet, and Sheet C is another set sheet. So Sheet A (the first stage in the process) could be any one of 12 possible sheets.

 

[Alex Blakenburg]

I got that but you also say that macro 1 returns to sheet to one of Sheet 1-12, is this the same as at the starting Sheet 1-12.

Absolutely key point is positioning of the Public statement, I still need that confirmed.

 

[Dataframe]

I got that but you also say that macro 1 returns to sheet to one of Sheet 1-12, is this the same as at the starting Sheet 1-12.

Absolutely key point is positioning of the Public statement, I still need that confirmed.

Yes, whichever of the sheets 1-12 the Macro 1 is run from, it goes back to that initial sheet.

The Public Statement is in ThisWorkbook module.