| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rename worksheet tabs | Excel Worksheet Functions | |||
| I want to rename my worksheet tabs... | Excel Discussion (Misc queries) | |||
| Rename worksheet tabs | Excel Programming | |||
| rename tabs | Excel Discussion (Misc queries) | |||
| Rename existing tabs | Excel Programming | |||
Hybrid Mode
