Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change XL sheet names from cell range
I have been trying unsuccessfully to find a macro that will get data from a
cell range on one sheet and change the worksheet names on another 10 sheets to each of the names in that cell range. Can anyone offer a solution please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change XL sheet names from cell range
Thanks for the very prompt reply. I seem to be missing a bit of knowledge
tho' - doesn't matter what I've tried to do with your code { a) created a macro and pasted it in... b)Alt+F11 for VBE and Inserted Module then pasted } Ihaven't been able to get a result. Also, the cell range in my case is Q14:Q23 do I change the code to be Target.Column=17 and Target.Value=??? "Bila" wrote: I have been trying unsuccessfully to find a macro that will get data from a cell range on one sheet and change the worksheet names on another 10 sheets to each of the names in that cell range. Can anyone offer a solution please? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change XL sheet names from cell range
Sub ChangeNames()
Dim j As Long Dim i As Long For j = 1 To Cells(Rows.Count, "A").End(xlUp).Row i = i + 1 If Worksheets(i).Name < ActiveSheet.Name Then Worksheets(i).Name = Cells(j, "A").Value Else j = j - 1 End If Next j End Sub put the code in a standard code module -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bila" wrote in message ... I have been trying unsuccessfully to find a macro that will get data from a cell range on one sheet and change the worksheet names on another 10 sheets to each of the names in that cell range. Can anyone offer a solution please? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change XL sheet names from cell range
Just noticed the bit about Q14:Q23, so use
Sub ChangeNames() Dim j As Long Dim i As Long For j = 14 To 23 i = i + 1 If Worksheets(i).Name < ActiveSheet.Name Then Worksheets(i).Name = Cells(j, "Q").Value Else j = j - 1 End If Next j End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bila" wrote in message ... Thanks for the very prompt reply. I seem to be missing a bit of knowledge tho' - doesn't matter what I've tried to do with your code { a) created a macro and pasted it in... b)Alt+F11 for VBE and Inserted Module then pasted } Ihaven't been able to get a result. Also, the cell range in my case is Q14:Q23 do I change the code to be Target.Column=17 and Target.Value=??? "Bila" wrote: I have been trying unsuccessfully to find a macro that will get data from a cell range on one sheet and change the worksheet names on another 10 sheets to each of the names in that cell range. Can anyone offer a solution please? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change XL sheet names from cell range
OK, we are getting somewhere - thankyou to both who have replied!
By somewhere, I mean that I have got the second lot of code (By Bob Phillips) to work, BUT I am usually only changing one of the 10 names so of course it is erroring when it tries to change the sheet to an existing name... SO, I then attempted to intergrate both Jim & Bob's code, using the error handling from Jims with the code I have working from Bob's - guess what... I am STILL not that clever! Could one of you please direct me in how to trap the error so the routine will work its way through the rest of the names, changing the worksheet name to any that I have altered? Thanks, Jim Cove (Bila) "Bob Phillips" wrote: Sub ChangeNames() Dim j As Long Dim i As Long For j = 1 To Cells(Rows.Count, "A").End(xlUp).Row i = i + 1 If Worksheets(i).Name < ActiveSheet.Name Then Worksheets(i).Name = Cells(j, "A").Value Else j = j - 1 End If Next j End Sub put the code in a standard code module -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bila" wrote in message ... I have been trying unsuccessfully to find a macro that will get data from a cell range on one sheet and change the worksheet names on another 10 sheets to each of the names in that cell range. Can anyone offer a solution please? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change XL sheet names from cell range
You could do it as worksheet change event as Jim suggests, b ut it is not
clear (at least to me) which row would pertain to which worksheet (they can get moved around). Simplest way is just to do this Sub ChangeNames() Dim oWsNext As Worksheet Dim oWs As Worksheet Dim iLastRow As Long Dim j As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Set oWsNext = Nothing On Error Resume Next Set oWsNext = Worksheets(Cells(i, "A").Value) On Error GoTo 0 If oWsNext Is Nothing Then j = 1 For Each oWs In ActiveWorkbook.Worksheets With oWs If ActiveSheet.Name < .Name Then If IsError(Application.Match(.Name, _ ActiveSheet.Range("A:A"), 0)) Then .Name = Cells(i, "A").Value Exit For End If End If End With Next oWs End If Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bila" wrote in message ... OK, we are getting somewhere - thankyou to both who have replied! By somewhere, I mean that I have got the second lot of code (By Bob Phillips) to work, BUT I am usually only changing one of the 10 names so of course it is erroring when it tries to change the sheet to an existing name... SO, I then attempted to intergrate both Jim & Bob's code, using the error handling from Jims with the code I have working from Bob's - guess what... I am STILL not that clever! Could one of you please direct me in how to trap the error so the routine will work its way through the rest of the names, changing the worksheet name to any that I have altered? Thanks, Jim Cove (Bila) "Bob Phillips" wrote: Sub ChangeNames() Dim j As Long Dim i As Long For j = 1 To Cells(Rows.Count, "A").End(xlUp).Row i = i + 1 If Worksheets(i).Name < ActiveSheet.Name Then Worksheets(i).Name = Cells(j, "A").Value Else j = j - 1 End If Next j End Sub put the code in a standard code module -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bila" wrote in message ... I have been trying unsuccessfully to find a macro that will get data from a cell range on one sheet and change the worksheet names on another 10 sheets to each of the names in that cell range. Can anyone offer a solution please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet tab names change automatically | Excel Discussion (Misc queries) | |||
Change sheet names for charts on the fly | Excel Discussion (Misc queries) | |||
How to change sheet names via formula | Excel Discussion (Misc queries) | |||
Help, change range names back to cell references? | New Users to Excel | |||
Range Names From one sheet to Another | Excel Programming |