Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
I am trying to make 87 sheets in a workbook change their name to reflect that
of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe first one and here's the code I've entered: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B1" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub I just right clicked the tab, clicked "view code" and inserted this (it's from a previous post. I'm a novice, but am working on it). Assuming this can be made to work, is there a way to make this loop through all the sheets instead of doing this one at a time? This will be a monthly task. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
Since you can't name each sheet the same thing, you will need to tell us how
you want to apply what is in B1 to the existing sheet names (concatenate it on to the beginning or end, replace some existing text, something else entirely?). Rick "Bill_Green via OfficeKB.com" <u45230@uwe wrote in message news:882c47cb29ac4@uwe... I am trying to make 87 sheets in a workbook change their name to reflect that of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe first one and here's the code I've entered: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B1" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub I just right clicked the tab, clicked "view code" and inserted this (it's from a previous post. I'm a novice, but am working on it). Assuming this can be made to work, is there a way to make this loop through all the sheets instead of doing this one at a time? This will be a monthly task. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
How many worksheets are in this workbook--87? (Any worksheets to avoid???)
Do you want each worksheet named after what's found in B1 of that particular worksheet? Do the values in B1 change often? If yes, then you could use a workbook event (instead of 87 separat worksheet events) -- but that would depend on knowing the names of the sheets to be avoided, too. If no, then you could use a macro that would cycle through the worksheets and change the names accordingly--and if the values in B1 change, you could just run the macro when you wanted to. You wouldn't need excel to always be looking for a change. "Bill_Green via OfficeKB.com" wrote: I am trying to make 87 sheets in a workbook change their name to reflect that of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe first one and here's the code I've entered: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B1" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub I just right clicked the tab, clicked "view code" and inserted this (it's from a previous post. I'm a novice, but am working on it). Assuming this can be made to work, is there a way to make this loop through all the sheets instead of doing this one at a time? This will be a monthly task. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
I was able to get it to apply to all sheets, and yes it needs to pull from B1
on every sheet. Can I get it to hit only the selected sheets? Here's the code I have now: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/4/2008 by bgreen Dim sh As Worksheet For i = 1 To Sheets.Count Set sh = Sheets(i) sh.Activate With ActiveSheet .Name = .Range("B1").Value End With Next i End Sub The actual name of the product is in cell A1, but it's very long and the first 15 digits are the same or close to it on most of them, so I selected the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to get past the digits that are the same and since the name of the tab has to be less than 31 characters long, then copied and pasted special "values." This is a lot of steps. Dave Peterson wrote: How many worksheets are in this workbook--87? (Any worksheets to avoid???) Do you want each worksheet named after what's found in B1 of that particular worksheet? Do the values in B1 change often? If yes, then you could use a workbook event (instead of 87 separat worksheet events) -- but that would depend on knowing the names of the sheets to be avoided, too. If no, then you could use a macro that would cycle through the worksheets and change the names accordingly--and if the values in B1 change, you could just run the macro when you wanted to. You wouldn't need excel to always be looking for a change. I am trying to make 87 sheets in a workbook change their name to reflect that of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe [quoted text clipped - 21 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
By the way, the code is in a regular macro now, not the sheet coding (I can't
make that work anyway). Bill_Green wrote: I was able to get it to apply to all sheets, and yes it needs to pull from B1 on every sheet. Can I get it to hit only the selected sheets? Here's the code I have now: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/4/2008 by bgreen Dim sh As Worksheet For i = 1 To Sheets.Count Set sh = Sheets(i) sh.Activate With ActiveSheet .Name = .Range("B1").Value End With Next i End Sub The actual name of the product is in cell A1, but it's very long and the first 15 digits are the same or close to it on most of them, so I selected the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to get past the digits that are the same and since the name of the tab has to be less than 31 characters long, then copied and pasted special "values." This is a lot of steps. How many worksheets are in this workbook--87? (Any worksheets to avoid???) [quoted text clipped - 17 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
Give this macro a try...
Sub ChangeNameOnSelectedSheets() Dim SH As Worksheet For Each SH In ActiveWindow.SelectedSheets SH.Name = SH.Range("B1").Value Next End Sub Rick "Bill_Green via OfficeKB.com" <u45230@uwe wrote in message news:882ce8a1de2d6@uwe... I was able to get it to apply to all sheets, and yes it needs to pull from B1 on every sheet. Can I get it to hit only the selected sheets? Here's the code I have now: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/4/2008 by bgreen Dim sh As Worksheet For i = 1 To Sheets.Count Set sh = Sheets(i) sh.Activate With ActiveSheet .Name = .Range("B1").Value End With Next i End Sub The actual name of the product is in cell A1, but it's very long and the first 15 digits are the same or close to it on most of them, so I selected the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to get past the digits that are the same and since the name of the tab has to be less than 31 characters long, then copied and pasted special "values." This is a lot of steps. Dave Peterson wrote: How many worksheets are in this workbook--87? (Any worksheets to avoid???) Do you want each worksheet named after what's found in B1 of that particular worksheet? Do the values in B1 change often? If yes, then you could use a workbook event (instead of 87 separat worksheet events) -- but that would depend on knowing the names of the sheets to be avoided, too. If no, then you could use a macro that would cycle through the worksheets and change the names accordingly--and if the values in B1 change, you could just run the macro when you wanted to. You wouldn't need excel to always be looking for a change. I am trying to make 87 sheets in a workbook change their name to reflect that of what is in cell B1. Is there a way to do that in VBA? I've tried it onthe [quoted text clipped - 21 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
Looks like that will work. Thanks! I appreciate it.
Rick Rothstein (MVP - VB) wrote: Give this macro a try... Sub ChangeNameOnSelectedSheets() Dim SH As Worksheet For Each SH In ActiveWindow.SelectedSheets SH.Name = SH.Range("B1").Value Next End Sub Rick I was able to get it to apply to all sheets, and yes it needs to pull from B1 [quoted text clipped - 56 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell A1 to sheet name
If you want to name all the worksheets based on what's in A1 (you could drop the
formula in B1): Option Explicit Sub Macro2A() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets On Error Resume Next sh.Name = Mid(sh.Range("a1").Value, 16, 31) If Err.Number < 0 Then Err.Clear MsgBox "Sheet: " & sh.Name & vbLf & "wasn't renamed" End If On Error GoTo 0 Next sh End Sub "Bill_Green via OfficeKB.com" wrote: By the way, the code is in a regular macro now, not the sheet coding (I can't make that work anyway). Bill_Green wrote: I was able to get it to apply to all sheets, and yes it needs to pull from B1 on every sheet. Can I get it to hit only the selected sheets? Here's the code I have now: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/4/2008 by bgreen Dim sh As Worksheet For i = 1 To Sheets.Count Set sh = Sheets(i) sh.Activate With ActiveSheet .Name = .Range("B1").Value End With Next i End Sub The actual name of the product is in cell A1, but it's very long and the first 15 digits are the same or close to it on most of them, so I selected the appropriate sheets and put this formula in cell B1 '=MID(A1,16,31)' to get past the digits that are the same and since the name of the tab has to be less than 31 characters long, then copied and pasted special "values." This is a lot of steps. How many worksheets are in this workbook--87? (Any worksheets to avoid???) [quoted text clipped - 17 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200808/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copying data from a cell on sheet to a diff cell/sheet | Excel Worksheet Functions | |||
copy a sum in cell on sheet 1 (not formula) to cell on sheet 2 | Excel Worksheet Functions | |||
copy a sum in cell on sheet 1 (not formula) to cell on sheet 2 | Excel Worksheet Functions | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
Excell:Move from any Cell Sheet 1 to any cell Sheet 2 etc. | Excel Worksheet Functions |