Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
In cell A1 I have the year 2008.
In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
Hi,
try Option Explicit Sub testme() Dim iRow as long with activesheet for irow = 1 to .cells(.rows.count,"A").end(xlup).row sheets(irow).name = .cells(irow,"A").value next irow end with End with This could fail if you have invalid names in column A and/or there are duplicate names or sheets with the same name already existing. Or if you don't have enough sheets! "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in
workbook (in case you have additional sheets) This is a worksheet event, so right click on summary sheet tab and click "view code". Paste this in. '=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 then exit sub End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
opps, use this
Maybe something like: Option Explicit Sub testme() Dim iRow as long with activesheet for irow = 2 to .cells(.rows.count,"A").end(xlup).row sheets(irow).name = .cells(irow,"A").value next irow end with End Sub "Eduardo" wrote: Hi, try Option Explicit Sub testme() Dim iRow as long with activesheet for irow = 1 to .cells(.rows.count,"A").end(xlup).row sheets(irow).name = .cells(irow,"A").value next irow end with End with This could fail if you have invalid names in column A and/or there are duplicate names or sheets with the same name already existing. Or if you don't have enough sheets! "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
Your assumptions are correct. The code worked fine up until I typed in
another date in A1 and got a run-time error that told me that I could not rename a sheet to the same name as another sheet. I understand the problem but need help on a solution. For example, if I change the date in A1 from 2009 to 2010, a conflict occurs when the code tries to rename the TAB labeled 2009 to 2010 because the next worksheet in the sequence already has that name. "Luke M" wrote: Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in workbook (in case you have additional sheets) This is a worksheet event, so right click on summary sheet tab and click "view code". Paste this in. '=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 then exit sub End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
Hi Johnny,
did you try my code that works, as it rename again each sheet, attach a code to a button so you can run it from there "Johnny" wrote: Your assumptions are correct. The code worked fine up until I typed in another date in A1 and got a run-time error that told me that I could not rename a sheet to the same name as another sheet. I understand the problem but need help on a solution. For example, if I change the date in A1 from 2009 to 2010, a conflict occurs when the code tries to rename the TAB labeled 2009 to 2010 because the next worksheet in the sequence already has that name. "Luke M" wrote: Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in workbook (in case you have additional sheets) This is a worksheet event, so right click on summary sheet tab and click "view code". Paste this in. '=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 then exit sub End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
Yes I did but didn't get it to work. I am going to try it again and see if I
can figure it out. I'll get back to you. "Eduardo" wrote: Hi Johnny, did you try my code that works, as it rename again each sheet, attach a code to a button so you can run it from there "Johnny" wrote: Your assumptions are correct. The code worked fine up until I typed in another date in A1 and got a run-time error that told me that I could not rename a sheet to the same name as another sheet. I understand the problem but need help on a solution. For example, if I change the date in A1 from 2009 to 2010, a conflict occurs when the code tries to rename the TAB labeled 2009 to 2010 because the next worksheet in the sequence already has that name. "Luke M" wrote: Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in workbook (in case you have additional sheets) This is a worksheet event, so right click on summary sheet tab and click "view code". Paste this in. '=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 then exit sub End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
Eduardo,
I got the same error when using your code regarding being able to rename a sheet to the same name as another sheet. "Johnny" wrote: Yes I did but didn't get it to work. I am going to try it again and see if I can figure it out. I'll get back to you. "Eduardo" wrote: Hi Johnny, did you try my code that works, as it rename again each sheet, attach a code to a button so you can run it from there "Johnny" wrote: Your assumptions are correct. The code worked fine up until I typed in another date in A1 and got a run-time error that told me that I could not rename a sheet to the same name as another sheet. I understand the problem but need help on a solution. For example, if I change the date in A1 from 2009 to 2010, a conflict occurs when the code tries to rename the TAB labeled 2009 to 2010 because the next worksheet in the sequence already has that name. "Luke M" wrote: Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in workbook (in case you have additional sheets) This is a worksheet event, so right click on summary sheet tab and click "view code". Paste this in. '=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 then exit sub End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Names to Match Cell Values when Changed
We can get around that by temporarily renaming each sheet to something else.
'=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = ws.Name & "PLACEHOLDER" i = i + 1 End If If i 4 Then Exit For Next i = 1 For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 Then Exit For End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: Your assumptions are correct. The code worked fine up until I typed in another date in A1 and got a run-time error that told me that I could not rename a sheet to the same name as another sheet. I understand the problem but need help on a solution. For example, if I change the date in A1 from 2009 to 2010, a conflict occurs when the code tries to rename the TAB labeled 2009 to 2010 because the next worksheet in the sequence already has that name. "Luke M" wrote: Assumptions: Summary sheet and the 4 "year" sheets are the first 5 sheets in workbook (in case you have additional sheets) This is a worksheet event, so right click on summary sheet tab and click "view code". Paste this in. '=========== Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet i = 1 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub For Each ws In Worksheets If ws.Name < Me.Name Then ws.Name = Me.Cells(i, "A") i = i + 1 'In case you have other sheets past the 4 years If i 4 then exit sub End If Next End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johnny" wrote: In cell A1 I have the year 2008. In cells A2 through A4 I have a formula to add one year as I move down each cell so that in A2 would be 2009, A3-2010 and so on. I also four worksheets that are named for each of those years. I would like to have VBA code to rename the TAB Names when I change cell A1 which will in turn change each for the four TAB Names to match whats in cells A1 - A4 respectively. Hope my question is clear enough for someone to help me come up with a solution. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The column names (A,B,C, etc...) changed to numbers?????????? | Setting up and Configuration of Excel | |||
Cell values have changed | Excel Discussion (Misc queries) | |||
tab names from cell values | Excel Discussion (Misc queries) | |||
Compare and match names and extract a cell content | Excel Worksheet Functions | |||
The columns names on my sheet have changed!! | Excel Discussion (Misc queries) |