How to Get & Use Worksheet Name in Excel2007 Macro

[jarhtmd]

I have an Excel2007 workbook with several worksheets. Most of the sheets are named with the 4-digit year (2017, 2018, etc).
I recorded a macro to copy a worksheet and then do various things in the new worksheet. I would like to refer to [whichever sheet I'm working in] instead of "2018" & "2018 (2)" shown in the coding below, so that I don't have to have a macro for each year. How do I do that?

Sub CopyAndRenameSheet()
    Sheets("2018").Select
    Sheets("2018").Copy Before:=Sheets(1)
    Sheets("2018 (2)").Select
    Sheets("2018 (2)").Name = "2018 NewSheetName"
    Sheets("2018 NewSheetName").Select
    ActiveSheet.Unprotect
   (do other stuff . . . )
End Sub

[Kenneth Hobson]

Sub CopyAndRenameSheet()
    Sheets("2018").Copy Before:=Sheets(1)
    With ActiveSheet
      .Name = "2018 NewSheetName"
      .Unprotect
      '(do other stuff . . . )
    End With
End Sub

I would check if the sheet name exists before setting it though.

[jarhtmd]

Sorry, I didn't explain the situation clearly enough. Plus I made a posting error. The code that I posted was recorded while working in "2018" worksheet. I right-clicked the worksheet name tab at bottom; selected "Move or Copy" and checked "Create a Copy". That created macro coding using the name of that worksheet (2018). Actually, I errored when I changed the new sheet name for posting. That just muddied the waters. Sorry. I should NOT have included "2018 NewSheetName", although that is what I will eventually want. But I'm not asking about that now. If I'm able to capture the source worksheet name, I think I'll be able to figure out how to use that in the final sheet name. If not, I'll post a request for help with that.


I just repeated the recording process for "2017" worksheet. and got:

Sub CopyAndRenameLastYear()
    Sheets("2017").Select
    Sheets("2017").Copy Before:=Sheets(1)
    Sheets("2017 (2)").Select
    Sheets("2017 (2)").Name = "NewSheetName"
    Sheets("NewSheetName").Select
    ActiveSheet.Unprotect
   (do other stuff . . . )
End Sub

I want to modify that to say . . .

Sub CopyAndRenameSheet()
    Sheets("{This Sheet}").Select
    Sheets("{This Sheet}").Copy Before:=Sheets(1)
    Sheets("{This Sheet} (2)").Select
    Sheets("{This Sheet} (2)").Name = "New{This Sheet}Sheet" . . . . (or similar)
    Sheets("NewSheetName").Select
    ActiveSheet.Unprotect
   (do other stuff . . . )
End Sub

I hope that is clearer.

[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