rename sheets(tabs)

[Loop]

Hi All,

Is it possible to rename sheets(tabs) in a workbook taking value from
cell A3 of each sheet.
Every month I download an excel file with certain amount of sheets.
All I want is to rename Sheets: Page 1, Page 2, ......and so on. It
has to be sheets named: 71410200006, 71410200008,.......and so on. In
a cell A3 of each sheet I have: 71410200008 (COM)MARLBOROUGH PATIENT
SERV CENTRES, 71410200009 (COM)GULF PATIENT SERV CENTRES,...and so on.
So it has to take only numbers from cell A3 to rename a sheet, then
next sheet. Plus sort only renamed sheets.

Thanks in advance

 

[Rick Rothstein]

Give this macro a try (I'm assuming all the text in A3 starts with 11 digits
followed by a space and then text)...

Sub ChangeSheetNames()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.Range("A3").Value Like "########### *" Then
WS.Name = Val(WS.Range("A3").Value)
End If
Next
End Sub

 

[ryguy7272]

Untested, but I think this will do it:

Dim WkSht As Worksheet
For Each WkSht In Worksheets
WkSht.Name = WkSht.Range("A3").Value
Next WkSht

HTH,
Ryan---

 

[Rick Rothstein]

That code will fail if any of the sheets have nothing in cell A3. It will
also rename *every* sheet in the workbook, even those with "normal" entries
in them (such as perhaps a Main or Summary sheet).

 

[Rick Rothstein]

I just noticed your last sentence where you say you want "only renamed
sheets" sorted. Did you mean to sort them even if they are out of sort order
with the existing sheets? Also, if there are existing sheets and you do only
want sort the "new" sheets (no matter that they will be out of order with
the existing sheets), then is there a way for a macro to know what the last
existing worksheet was before you added your new sheets? If not, then they
can't be sorted individually because there will be no way to tell existing
sheets from new sheets. Finally, are there any other sheets in the workbook
that do not have your "special" text in cell A3, such as a Main sheet, a
Summary sheet, etc.?

 

[Loop]

Untested, but I think this will do it:

Dim WkSht As Worksheet
For Each WkSht In Worksheets
 WkSht.Name = WkSht.Range("A3").Value
Next WkSht

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..







- Show quoted text -

Works perfect. Thanks a lo.

 

[Rick Rothstein]

Before you say it, yes, I know the OP said "each" sheet, but I don't think
he has given us the complete picture of his workbook (there are existing
sheets in it and he is adding new sheets to that). I have asked the OP for
clarification on this point in a follow up post to my own posting.

 

[Loop]

I just noticed your last sentence where you say you want "only renamed
sheets" sorted. Did you mean to sort them even if they are out of sort order
with the existing sheets? Also, if there are existing sheets and you do only
want sort the "new" sheets (no matter that they will be out of order with
the existing sheets), then is there a way for a macro to know what the last
existing worksheet was before you added your new sheets? If not, then they
can't be sorted individually because there will be no way to tell existing
sheets from new sheets. Finally, are there any other sheets in the workbook
that do not have your "special" text in cell A3, such as a Main sheet, a
Summary sheet, etc.?

Rick, Thanks a lot, everything works as I wanted.

 

[Loop]

Before you say it, yes, I know the OP said "each" sheet, but I don't think
he has given us the complete picture of his workbook (there are existing
sheets in it and he is adding new sheets to that). I have asked the OP for
clarification on this point in a follow up post to my own posting.

--
Rick (MVP - Excel)






- Show quoted text -

Other sheets do not have those values.

 

[Rick Rothstein]

Is it possible to rename sheets(tabs) in a workbook taking value from

Other sheets do not have those values.

I'm not sure what your response means. Are you saying that you have sheets
in the workbook that do not have entries starting with 11 digits and a space
followed by text (your service center names I'm guessing)? If so, then you
better test the code that Ryan posted carefully as it doesn't check for this
before renaming the sheet. So, I'm still not clear on how to tell existing
from new sheets... perhaps your response to the above will clarify it for
me.

 

[Loop]

I'm not sure what your response means. Are you saying that you have sheets
in the workbook that do not have entries starting with 11 digits and a space
followed by text (your service center names I'm guessing)? If so, then you
better test the code that Ryan posted carefully as it doesn't check for this
before renaming the sheet. So, I'm still not clear on how to tell existing
from new sheets... perhaps your response to the above will clarify it for
me.

Rick, I just made some clarification. The code, that you created,
works as I wanted. It does not sort the sheets however, but I do not
need it. They are in order.

Thanks,