Macro to name sheets (Excel97 SR2)

Topic

New Reply

I have an easy one for you (easy for someone). I need a macro to name sheets in a workbook. I am referring to the name that appears on the tab. I have seen this here before but have not able to find it again.
I have an Excel97 Workbook that is created by an Addin. It is populated with information from a budget database (not Excel). It has over 100 worksheets and the sheets are named by the addin. The names are NOT very meaningful.
All I want to do is have a macro cycle thru the sheets and rename each sheet (except the first sheet) by taking the first 5 characters of the contents of cell A2 (always cell A2 and each sheet has different contents) and concatenate “Total” to the end.
If cell A2 of the sheet contains “75030 Rosemount Maintenance” the sheet should be named “75030Total”. I create many reports using this addin and it would be nice to be able to easily rename the tabs each time I create a new report.

Thank you for assisting fellow users thru this forum.

Chuck

Reply | Quote

Replies

assuming that the first sheet is always the first sheet, I believe this will do what you want:

Sub Macro1()
Dim i As Integer
For i = 2 To ActiveWorkbook.Sheets.Count
Sheets(i).Name = Left(Sheets(i).Cells(2, 1).Value, 5) & “total”
Next
End Sub

Reply | Quote

Brooke,

Not only fast but accurate! It worked so fast I almost did not realize that it accomplished the task. I thank you.
I just wish it looked more complicated That was embarrasingly simple. (and I spent about an hour on it and kept failing)
I LOVE THIS PLACE!

Chuck

Reply | Quote