Temporary Sheet Name

[Keep It Simple Stupid]

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???

 

[bamblack]

Try setting a variable to ActiveSheet.Name like this

Sub test

Dim XXX as String

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

End Sub

 

[Keep It Simple Stupid]

This is perfect~!!!!