Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spreadsheet name = cell content?
Hi - can a spreadsheet name within a workbook be told to reflect a cell
value please? I have a list of suppliers in a column on a control sheet. this list is dynamic due to adding new suppliers and i would like a spreadsheet for each supplier in alphabetical order. I'm thinking that if the spreadsheet names could be = to a cell content, the workbook will always be alphabetical. TIA Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spreadsheet name = cell content?
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<=== change to suit Dim sh As Worksheet On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Set sh = Worksheets(Target.Value) On Error GoTo 0 If sh Is Nothing Then Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name = Target.Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Hi - can a spreadsheet name within a workbook be told to reflect a cell value please? I have a list of suppliers in a column on a control sheet. this list is dynamic due to adding new suppliers and i would like a spreadsheet for each supplier in alphabetical order. I'm thinking that if the spreadsheet names could be = to a cell content, the workbook will always be alphabetical. TIA Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spreadsheet name = cell content?
Bob - thanks for the quick response, but if I may check a couple of things..
1. I can make a cell on every spreadsheet relate to a cell in the list on another spreadsheet, C2=Control O6 etc. So, do I still need Const WS_RANGE As String = "A1:A10" and, if so, can the String be a single cell value like C2 in the same spreadsheet? Indeed is it still a String? 2. When I want to paste the code I choose View Code and then see an empty window on the right side of the screen. |Is that where I paste it, without selecting anything else? Sorry to be dense Chris "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A10" '<=== change to suit Dim sh As Worksheet On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Set sh = Worksheets(Target.Value) On Error GoTo 0 If sh Is Nothing Then Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name = Target.Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Hi - can a spreadsheet name within a workbook be told to reflect a cell value please? I have a list of suppliers in a column on a control sheet. this list is dynamic due to adding new suppliers and i would like a spreadsheet for each supplier in alphabetical order. I'm thinking that if the spreadsheet names could be = to a cell content, the workbook will always be alphabetical. TIA Chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spreadsheet name = cell content?
You seem, to be asking for a slightly different solution now.
I provided a solution to build worksheets from a list on one sheet, but you seem to be saying that you will add the worksheets, and will link a fixed cell on those worksheet to that list, and just want the worksheet name to reflect the value in that common cell. Is that a correct interpretation? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Bob - thanks for the quick response, but if I may check a couple of things.. 1. I can make a cell on every spreadsheet relate to a cell in the list on another spreadsheet, C2=Control O6 etc. So, do I still need Const WS_RANGE As String = "A1:A10" and, if so, can the String be a single cell value like C2 in the same spreadsheet? Indeed is it still a String? 2. When I want to paste the code I choose View Code and then see an empty window on the right side of the screen. |Is that where I paste it, without selecting anything else? Sorry to be dense Chris "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A10" '<=== change to suit Dim sh As Worksheet On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Set sh = Worksheets(Target.Value) On Error GoTo 0 If sh Is Nothing Then Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name = Target.Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Hi - can a spreadsheet name within a workbook be told to reflect a cell value please? I have a list of suppliers in a column on a control sheet. this list is dynamic due to adding new suppliers and i would like a spreadsheet for each supplier in alphabetical order. I'm thinking that if the spreadsheet names could be = to a cell content, the workbook will always be alphabetical. TIA Chris |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spreadsheet name = cell content?
Bob, I can only apologise for my inability to explain clearly. No excuses, I
had not made it clear. I definately need to provide the worksheets myself because they will all be filled with various formulae to extrapolate data based on the content of cell C2. As that will be the same as the name of the spreadsheet, I wondered if that cell could be used to name the spreadsheet. It all shows my knowledge of VB or whatever that code is, is zilch 'cos i thought that was what you had done for me! TIA Chris "Bob Phillips" wrote in message ... You seem, to be asking for a slightly different solution now. I provided a solution to build worksheets from a list on one sheet, but you seem to be saying that you will add the worksheets, and will link a fixed cell on those worksheet to that list, and just want the worksheet name to reflect the value in that common cell. Is that a correct interpretation? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Bob - thanks for the quick response, but if I may check a couple of things.. 1. I can make a cell on every spreadsheet relate to a cell in the list on another spreadsheet, C2=Control O6 etc. So, do I still need Const WS_RANGE As String = "A1:A10" and, if so, can the String be a single cell value like C2 in the same spreadsheet? Indeed is it still a String? 2. When I want to paste the code I choose View Code and then see an empty window on the right side of the screen. |Is that where I paste it, without selecting anything else? Sorry to be dense Chris "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A10" '<=== change to suit Dim sh As Worksheet On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Set sh = Worksheets(Target.Value) On Error GoTo 0 If sh Is Nothing Then Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name = Target.Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Tester" wrote in message ... Hi - can a spreadsheet name within a workbook be told to reflect a cell value please? I have a list of suppliers in a column on a control sheet. this list is dynamic due to adding new suppliers and i would like a spreadsheet for each supplier in alphabetical order. I'm thinking that if the spreadsheet names could be = to a cell content, the workbook will always be alphabetical. TIA Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create a link for both content and format of the cell? | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
One spreadsheet cell won't print but shows up in print preview? | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |