rename sheets(tabs)
 

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

rename sheets(tabs)
 

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

--
Rick (MVP - Excel)


"Loop" wrote in message
...
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

rename sheets(tabs)
 

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''.


"Loop" wrote:

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

rename sheets(tabs)
 

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 (MVP - Excel)


"ryguy7272" wrote in message
...
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''.


"Loop" wrote:

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

rename sheets(tabs)
 

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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Loop" wrote in message
...
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

rename sheets(tabs)
 

On Aug 19, 10:31*am, ryguy7272
wrote:
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''..



"Loop" wrote:
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- Hide quoted text -

- Show quoted text -

Works perfect. Thanks a lo.

rename sheets(tabs)
 

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)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"ryguy7272" wrote in message
...
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''.


"Loop" wrote:

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

rename sheets(tabs)
 

On Aug 19, 10:50*am, "Rick Rothstein"
wrote:
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 (MVP - Excel)

"Rick Rothstein" wrote in message

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

rename sheets(tabs)
 

On Aug 19, 10:53*am, "Rick Rothstein"
wrote:
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)

"Rick Rothstein" wrote in message

...



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 (MVP - Excel)

"ryguy7272" wrote in message
...
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''.

"Loop" wrote:

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- Hide quoted text -

- Show quoted text -

Other sheets do not have those values.

rename sheets(tabs)
 

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- Hide quoted text -

- Show quoted text -

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.

--
Rick (MVP - Excel)

rename sheets(tabs)
 

On Aug 19, 11:23*am, "Rick Rothstein"
wrote:
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- Hide quoted text -

- Show quoted text -

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.

--
Rick (MVP - Excel)

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,