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

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

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

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

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