Forum Discussion
Help with worksheet name
I have sent out a template to many people in the organization. Next I want to aggregate results when the workbooks come in. To facilitate this, I want to change name of the sheets based on the value they entered in Cell C2 of Sheet1. How can I do it most efficiently?
Hi Ajay,
That's by macro, the sampe is here https://support.microsoft.com/en-us/help/2790229/step-by-step-rename-excel-sheet-with-cell-contents-by-using-macro
Perhaps Power Query could be an alternative for the aggregation, but that's another story.
3 Replies
Hi Ajay,
That's by macro, the sampe is here https://support.microsoft.com/en-us/help/2790229/step-by-step-rename-excel-sheet-with-cell-contents-by-using-macro
Perhaps Power Query could be an alternative for the aggregation, but that's another story.
Thanks Sergei. This article address a very limited aspect of my issue. The macro referenced by you has two severe limitations:
1. The original workbook's sheets are required to be defaults - i.e. Sheet1, sheet2 etc...
2. The macro would rename the current sheet then it is done.
In my case...I have renamed the sheets to be meaningful to my organization's people i.e. the sheets are labeled Rev, Metrics, T&E etc. When the workbook comes back, I would like to run a macro and rename all the sheets in the workbook as RevName1, MetricsName1, and T&EName1.
Probably a more comlicated macro. But it would be helpful and time saving in my case. Thanks!
Hi Ajay,
I don't see such limitations. If you have the workbook and in your first sheet (doesn't matter what's its name) in C2 you have the string to add to current sheets names, slightly modified macro
Sub RenameSheet() Dim rs As Worksheet Dim sName As String sName = Worksheets(1).Range("C2") For Each rs In Sheets rs.Name = rs.Name & sName Next rs End Subrenames all sheets in your workbook.
