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:
Code:
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 . . .
Code:
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.
Bookmarks