Temporary Sheet Name

Joined
May 24, 2007
Messages
26
I have 20 sheets (with variable sheet names, but we will call them Sheets 1-20 for now). On each of these sheets, there will be a button to trigger a macro.

The macro will have the user find an outside file, copy that sheet (rename it as Sheet AAA)into the original workbook (inserted into the first tab position - so now I will have Sheet AAA and then Sheet 1, Sheet 2, ...Sheet 20).

If I click the button in Sheet 1, I want to insert a formula that looks up data on Sheet AAA and returns it to Sheet 1. If I click the button on Sheet 7, I want to insert a formula on Sheet 7 that looks up data on Sheet AAA and returns it on Sheet 7. The problem is, when I rename the sheet I just inserted, I have inevitably clicked away from my "active sheet" so I don't know how to get back to the sheet I was originally in when I triggered the macro. I would hate to have to make 20 different macros that are the same macro, just referencing different sheets.

Any ideas???
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

bamblack

Board Regular
Joined
Aug 15, 2011
Messages
208
Try setting a variable to ActiveSheet.Name like this

Code:
Sub test

Dim XXX as String

XXX = ActiveSheet.Name
Sheets("SheetAAA").Select
Sheets(XXX).Select

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top