How to Get & Use Worksheet Name in Excel2007 Macro

[DocAElstein]

Hi jarhtmd,
If I understand correctly, then I think the answer to your question is quite simple.

You need to understand a bit about the worksheets .Name property, and the active worksheet, ( Activesheet ):

.Name property
As I understand it, the Worksheets .Name property can be applied to any Worksheet including the Activesheet. It can be used to assign the tab Name of a worksheet as Kenneth Hobson did in post #2. It can also be used to obtain the name from a given worksheet, such as the Activesheet. This latter point may be that which answers your question.
So for example, the code below will copy the active Worksheet** , and it will give it a name , part of which contains the name of the Worksheet active at the time at which the code starts. The key point is the first bit in the code below which stores the current active worksheet name for later use in the code.
( **I am assuming that I understand that the active worksheet is what you mean by [whichever sheet I'm working in]. That way that you use is quite a nice way to describe it. I sometimes say […“the worksheet you are “looking” at “…] )

Activesheet:
So this is “whichever you are working in” or “the worksheet you are “looking” at”. It is the sheet that you have as active. So usually you do not need to Select it.
( Effectively, Selecting something makes it active. So this command is redundant: Activesheet.Select. ( However, I can tell you from experience, that there are some strange bugs in Excel and Excel VBA which are cured when you liberally use that command in a few places. ) )
One other thing to note is that as a new sheet is made, ( for example by something like ActiveSheet.Copy Before:=Sheets.Item(1) ) , this new sheet then becomes the active worksheet. So after this point you are referring to the new worksheet with Activesheet.

Code example:
If you would like me to explain any more of the code in detail or want any more help then please let me know

Option Explicit
Sub CopyActiveWorksheetGiveNewWorksheetPartOfItsName()
Dim lisOrgShtNme As String: Let lisOrgShtNme = ActiveSheet.Name
 ActiveSheet.Copy Before:=Sheets.Item(1) ' After this line the active worksheet is now the newly created one
 Let ActiveSheet.Name = "New " & lisOrgShtNme
' Do other stuff on new sheet (which remains active)
End Sub

The code can go in any code module.

Code run example:
If, for example, your active worksheet had the name “2020”, then the new created worksheet will have the name “New 2020”
This I had before running the code:
2020.JPG : https://imgur.com/l8XlV5j
2020.JPG
You will see that I have the worksheet “2020” active in the above screenshot.

This I then had after running the code:
2020.JPG : https://imgur.com/P9HyW5O
New 2020.JPG
You will note from the last screenshot that the new worksheet, “New 2020” , is now shown as the active worksheet: The code did not select it, but the creating of the worksheet automatically made it now the active worksheet. ( Possibly some hidden internal coding does Select the new worksheet after it is created. I don't know. I doubt anyone does, anymore... :-) !)
_.________________________


Alan