How to name a sheet from text in a cell in another sheet ?

[NickTheBatMan]

I am very much a novice with using Excel, I'd prefer to be doing this
in Access as that's what the data's aimed at in the long run, but the
db I'm being forced to use is not mine and I can't work that out
either - also work don't support Access so I can't use that to do the
data transfer I'm being asked to do... oh why did I say I'd do this

Down to the true business...

I have created a workbook and am trying to name the sheets from cells
in another sheet - all within the same workbook.

I have been digging about the net and everything I've found that looks
to be nearly right doesn't work

Anyone out there willing to try to help me with this one ?

 

[Ross Culver]

Nick, your question is almost impossible to decipher. However, you can
certainly use VBA to accomplish what your after, that is, if you're trying
to programmatically rename or add sheets based off a list of values on a
sheet.

Please describe more clearly what you're trying to accomplish.

Ross

 

[NickTheBatMan]

Nick, your question is almost impossible to decipher. However, you can
certainly use VBA to accomplish what your after, that is, if you're trying
to programmatically rename or add sheets based off a list of values on a
sheet.

Please describe more clearly what you're trying to accomplish.

Ross

Ok Ross, I'll try again.

What I've got is a worksheet called Headers and I've put names in
cells on it - that's cells B1 to B4 through to H1 to H4.

I have created 24 sheets to reflect these cells and wish to name the
sheets by the characters entered into these cells - I am also using
the words in the cells in this Headers sheet to populate other cells
in yet another sheet called Links.

The info I'm putting into the cells in the other sheets is going to be
reflected in the Links Sheet by having the correct cell in the linked
sheets reflected in this Links one, then the data in the Links sheet
is going to be used to populate the Access database so that the layout
reflects what we want...

As I said, it's all for this database which is not mine and massively
more complex than just this...

Sorry it's so complex, I do hope you follow this ?

Thanks for attempting to aid me...

Nick

 

[Otto Moehrbach]

Nick
This little macro will rename every sheet in the file by the list you
have. It will not rename the "Headers" or "Links" sheets. HTH Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[Ross Culver]

It would be better if all the names were in one column instead of spread
across A-H, but try something like this using VBA. I'm making some
assumptions here that might be wrong, such as:

1) The first sheet you need to rename is already named Sheet2 (presumably,
Sheet1 is the one you changed to "Headers").
2) You want to name the sheets the entire value that's in each cell. If
not, parse out what you do want to use.
3) That you have, indeed, already created the additional sheets.
Otherwise, I would have used code to add each sheet as needed.

This might not be exactly what you need, but will hopefully lead you in the
right direction.

private sub NameSheets
dim X as integer, SheetName as string
X = 1

'For col A
do while X < 5
SheetName = Sheets("Headers").range("A" & x).value
Sheets(x + 1).name = sheetname
X = X + 1
loop

X = 1
'For col B
do while X < 5
SheetName = Sheets("Headers").range("B" & x).value
Sheets(x + 5).name = sheetname
X = X + 1
loop

X = 1
'For col C
do while X < 5
SheetName = Sheets("Headers").range("C" & x).value
Sheets(x + 10).name = sheetname
X = X + 1
loop

etc.

end sub

 

[Ross Culver]

Yes, I like the use of the range even better!

Ross

 

[NickTheBatMan]

Nick
This little macro will rename every sheet in the file by the list you
have. It will not rename the "Headers" or "Links" sheets. HTH Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub

Thanks you Otto, where do I put it ?

Just off to bed at 22:00 for the first of four 05:00 alarms for work !
Shall catch up tomorrow through Google Groups...
Nick

 

[Otto Moehrbach]

Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane labeled
Project - VBA Project. If it's not there, click on View - Project Explorer.
Find your file name in the Project Explorer and click on it. Do Insert -
Module. This brings up a larger blank pane on the right. Paste the macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click on it.
Click on Run. See what happens. If you feel shaky doing this, send me your
file and I'll place the macro for you and maybe give you a button in the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto

 

[NickTheBatMan]

Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

 

[Otto Moehrbach]

Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what sheet?
Do you mean you wanted the code to fire when you changed the entry in some
cell? That's easy enough to do.
What buttons? Otto

Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

 

[NickTheBatMan]

Nick
    What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...".  What cell?  In what sheet?
Do you mean you wanted the code to fire when you changed the entry in some
cell?  That's easy enough to do.
What buttons?  Otto


Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do Insert -
Module. This brings up a larger blank pane on the right. Paste the macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click on it..
Click on Run. See what happens. If you feel shaky doing this, send me your
file and I'll place the macro for you and maybe give you a button in the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address. HTH

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing !

 

[Otto Moehrbach]

Nick
Up until now I thought I understood what you wanted. But now, I don't
know. You say:
"I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in each
cell. That you wanted a bunch of your existing sheets named the names in
this list, one name per sheet. What do you mean by "so that I only have to
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step procedure
of how you would do this if you were doing it manually. Leave nothing out!
Otto

Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what sheet?
Do you mean you wanted the code to fire when you changed the entry in some
cell? That's easy enough to do.
What buttons? Otto


Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do Insert -
Module. This brings up a larger blank pane on the right. Paste the macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click on
it.
Click on Run. See what happens. If you feel shaky doing this, send me
your
file and I'll place the macro for you and maybe give you a button in the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address. HTH

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing !

 

[NickTheBatMan]

Nick
    Up until now I thought I understood what you wanted.  But now, Idon't
know.  You say:
"I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in each
cell.  That you wanted a bunch of your existing sheets named the names in
this list, one name per sheet.  What do you mean by "so that I only haveto
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step procedure
of how you would do this if you were doing it manually.  Leave nothing out!
Otto

Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what sheet?
Do you mean you wanted the code to fire when you changed the entry in some
cell? That's easy enough to do.
What buttons? Otto

"NickTheBatMan" <[email protected]> wrote in message

Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

On 16 Apr, 23:36, "Otto Moehrbach" <[email protected]>
wrote:

Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do Insert-
Module. This brings up a larger blank pane on the right. Paste the macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click on
it.
Click on Run. See what happens. If you feel shaky doing this, send me
your
file and I'll place the macro for you and maybe give you a button in the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address. HTH

On Apr 16, 8:22 pm, "Otto Moehrbach" <[email protected]>
wrote:
Nick
This little macro will rename every sheet in the file by the list you
have. It will not rename the "Headers" or "Links" sheets. HTH Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub
Thanks you Otto, where do I put it ?
Just off to bed at 22:00 for the first of four 05:00 alarms for work!
Shall catch up tomorrow through Google Groups...
Nick- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing ! - Hide quoted text -

- Show quoted text -

Otto, sorry for the mix up as you say I got it totally wrong in what I
was wanting...

I am hoping that when I alter any of the cells B1-H4 in this Headers
sheet, that it will name the sheets from 3 onwards - the first 2 are
set as Links & Headers. Does that explain it better ?

I think as usual my poor way of putting what I want across is letting
me down and I'll just leave it that I have to remember to change the
sheet names manually...

Nick

 

[Otto Moehrbach]

Nick
Here are 2 macros that must both be in your file. Between the 2 of
them, they will rename all the sheets (except Headers and Links sheets) to
the names in B1:H4 of the Headers sheet. The trigger that will fire these
macros is a change in the content of any cell in the B1:H4 range.
You have already placed the RenameExistingSheets macro correctly and I
haven't changed it from what I sent you before. But the other macro (the
first macro below) is different in that it MUST be placed in a different
kind of module. That macro is a sheet macro and MUST be placed in the sheet
module of the Headers sheet. You can access that module by right-clicking
on the Headers sheet tab and selecting View Code. Paste that macro into
that module. "X" out of the VBE to return to your sheet. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B1:H4")) Is Nothing Then
Call RenameExistingSheets
End If
End Sub

Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub

Nick
Up until now I thought I understood what you wanted. But now, I don't
know. You say:
"I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in each
cell. That you wanted a bunch of your existing sheets named the names in
this list, one name per sheet. What do you mean by "so that I only have to
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step procedure
of how you would do this if you were doing it manually. Leave nothing out!
Otto

Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what
sheet?
Do you mean you wanted the code to fire when you changed the entry in
some
cell? That's easy enough to do.
What buttons? Otto

"NickTheBatMan" <[email protected]> wrote in message

Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

On 16 Apr, 23:36, "Otto Moehrbach" <[email protected]>
wrote:

Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do
Insert -
Module. This brings up a larger blank pane on the right. Paste the
macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click on
it.
Click on Run. See what happens. If you feel shaky doing this, send me
your
file and I'll place the macro for you and maybe give you a button in
the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address.
HTH

On Apr 16, 8:22 pm, "Otto Moehrbach" <[email protected]>
wrote:
Nick
This little macro will rename every sheet in the file by the list
you
have. It will not rename the "Headers" or "Links" sheets. HTH Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub
Thanks you Otto, where do I put it ?
Just off to bed at 22:00 for the first of four 05:00 alarms for work
!
Shall catch up tomorrow through Google Groups...
Nick- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing ! - Hide quoted text -

- Show quoted text -

Otto, sorry for the mix up as you say I got it totally wrong in what I
was wanting...

I am hoping that when I alter any of the cells B1-H4 in this Headers
sheet, that it will name the sheets from 3 onwards - the first 2 are
set as Links & Headers. Does that explain it better ?

I think as usual my poor way of putting what I want across is letting
me down and I'll just leave it that I have to remember to change the
sheet names manually...

Nick

 

[NickTheBatMan]

Nick
    Here are 2 macros that must both be in your file.  Between the 2of
them, they will rename all the sheets (except Headers and Links sheets) to
the names in B1:H4 of the Headers sheet.  The trigger that will fire these
macros is a change in the content of any cell in the B1:H4 range.
You have already placed the RenameExistingSheets macro correctly and I
haven't changed it from what I sent you before.  But the other macro (the
first macro below) is different in that it MUST be placed in a different
kind of module.  That macro is a sheet macro and MUST be placed in the sheet
module of the Headers sheet.  You can access that module by right-clicking
on the Headers sheet tab and selecting View Code.  Paste that macro into
that module.  "X" out of the VBE to return to your sheet.  Otto
Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      If IsEmpty(Target.Value) Then Exit Sub
      If Not Intersect(Target, Range("B1:H4")) Is Nothing Then
            Call RenameExistingSheets
      End If
End Sub

Sub RenameExistingSheets()
      Dim rSheetNames As Range
      Dim ws As Worksheet
      Dim c As Long
      Application.ScreenUpdating = False
      c = 1
      Sheets("Headers").Select
      Set rSheetNames = Range("B1:H4")
      For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "Headers" And ws.Name <> "Links" Then
                  ws.Name = rSheetNames(c).Value
                  c = c + 1
            End If
      Next ws
      Application.ScreenUpdating = True

Nick
Up until now I thought I understood what you wanted. But now, I don't
know. You say:
"I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in each
cell. That you wanted a bunch of your existing sheets named the names in
this list, one name per sheet. What do you mean by "so that I only have to
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step procedure
of how you would do this if you were doing it manually. Leave nothing out!
Otto

"NickTheBatMan" <[email protected]> wrote in message

On 17 Apr, 14:02, "Otto Moehrbach" <[email protected]>
wrote:

Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what
sheet?
Do you mean you wanted the code to fire when you changed the entry in
some
cell? That's easy enough to do.
What buttons? Otto
Many thanks Otto, that works - sort of...
I was hoping that it may work after I had changed the contents of the
cell and come out of it...
The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...
Nick
On 16 Apr, 23:36, "Otto Moehrbach" <[email protected]>
wrote:
Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do
Insert -
Module. This brings up a larger blank pane on the right. Paste the
macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click on
it.
Click on Run. See what happens. If you feel shaky doing this, send me
your
file and I'll place the macro for you and maybe give you a button in
the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address.
HTH

On Apr 16, 8:22 pm, "Otto Moehrbach" <[email protected]>
wrote:
Nick
This little macro will rename every sheet in the file by the list
you
have. It will not rename the "Headers" or "Links" sheets. HTH Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub
Thanks you Otto, where do I put it ?
Just off to bed at 22:00 for the first of four 05:00 alarms for work
!
Shall catch up tomorrow through Google Groups...
Nick- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing ! - Hide quoted text -

- Show quoted text -

Otto, sorry for the mix up as you say I got it totally wrong in what I
was wanting...

I am hoping that when I alter any of the cells B1-H4 in this Headers
sheet, that it will name the sheets from 3 onwards - the first 2 are
set as Links & Headers. Does that explain it better ?

I think as usual my poor way of putting what I want across is letting
me down and I'll just leave it that I have to remember to change the
sheet names manually...

Nick- Hide quoted text -

- Show quoted text -

Otto, thank you.
I've been very busy today and only just got chance to have a go...

I've followed your instructions and the about which I've added into
the Module of the Headers sheet is returning a Runtime Error 1004
All I've typed in is National Lines

When I debug it highlights this
ws.Name = rSheetNames(c).Value

I wish I knew what I was doing so that I didn't have to trouble you !
Many thanks again...
Nick

 

[Otto Moehrbach]

Nick
There is a problem with the code I wrote for you. If none of your
sheets are named any of the sheet names in B1:H4, and you type in a sheet
name in that range, the code will work fine. Otherwise you will get an
error because the code is trying to name a sheet a name that already exists
with another sheet, so I have to do some rewrite of the code.
Tell me this about how you use this file. If the entries in B1:H4 are to be
sheet names, is it that you change one of the names in B1:H4? Is that what
you do? If so then do you want just the one name that you replaced changed
in the sheet that had the old name?
I will add an error trap in the code to pick up on the fact that a name in
B1:H4 is already assigned to one of the sheets and the code will not try to
rename a sheet by that name.
Write back and tell me a bit more about how you use this file as regards the
sheet names and B1:H4.
If you wish, send me your file via email and we'll work it out with emails.
My email is (e-mail address removed). Remove the "extra" from this
address. Identify yourself as NickTheBatMan so I know who you are. HTH
Otto

Nick
Here are 2 macros that must both be in your file. Between the 2 of
them, they will rename all the sheets (except Headers and Links sheets) to
the names in B1:H4 of the Headers sheet. The trigger that will fire these
macros is a change in the content of any cell in the B1:H4 range.
You have already placed the RenameExistingSheets macro correctly and I
haven't changed it from what I sent you before. But the other macro (the
first macro below) is different in that it MUST be placed in a different
kind of module. That macro is a sheet macro and MUST be placed in the
sheet
module of the Headers sheet. You can access that module by right-clicking
on the Headers sheet tab and selecting View Code. Paste that macro into
that module. "X" out of the VBE to return to your sheet. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B1:H4")) Is Nothing Then
Call RenameExistingSheets
End If
End Sub

Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True

Nick
Up until now I thought I understood what you wanted. But now, I don't
know. You say:
"I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in
each
cell. That you wanted a bunch of your existing sheets named the names in
this list, one name per sheet. What do you mean by "so that I only have
to
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step
procedure
of how you would do this if you were doing it manually. Leave nothing
out!
Otto

"NickTheBatMan" <[email protected]> wrote in message

On 17 Apr, 14:02, "Otto Moehrbach" <[email protected]>
wrote:

Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what
sheet?
Do you mean you wanted the code to fire when you changed the entry in
some
cell? That's easy enough to do.
What buttons? Otto
Many thanks Otto, that works - sort of...
I was hoping that it may work after I had changed the contents of the
cell and come out of it...
The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...
Nick
On 16 Apr, 23:36, "Otto Moehrbach" <[email protected]>
wrote:
Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do
Insert -
Module. This brings up a larger blank pane on the right. Paste the
macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click
on
it.
Click on Run. See what happens. If you feel shaky doing this, send
me
your
file and I'll place the macro for you and maybe give you a button in
the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address.
HTH

On Apr 16, 8:22 pm, "Otto Moehrbach"
<[email protected]>
wrote:
Nick
This little macro will rename every sheet in the file by the list
you
have. It will not rename the "Headers" or "Links" sheets. HTH
Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub
Thanks you Otto, where do I put it ?
Just off to bed at 22:00 for the first of four 05:00 alarms for
work
!
Shall catch up tomorrow through Google Groups...
Nick- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing ! - Hide quoted text -

- Show quoted text -

Otto, sorry for the mix up as you say I got it totally wrong in what I
was wanting...

I am hoping that when I alter any of the cells B1-H4 in this Headers
sheet, that it will name the sheets from 3 onwards - the first 2 are
set as Links & Headers. Does that explain it better ?

I think as usual my poor way of putting what I want across is letting
me down and I'll just leave it that I have to remember to change the
sheet names manually...

Nick- Hide quoted text -

- Show quoted text -

Otto, thank you.
I've been very busy today and only just got chance to have a go...

I've followed your instructions and the about which I've added into
the Module of the Headers sheet is returning a Runtime Error 1004
All I've typed in is National Lines

When I debug it highlights this
ws.Name = rSheetNames(c).Value

I wish I knew what I was doing so that I didn't have to trouble you !
Many thanks again...
Nick

 

[NickTheBatMan]

Nick
There is a problem with the code I wrote for you. If none of your
sheets are named any of the sheet names in B1:H4, and you type in a sheet
name in that range, the code will work fine. Otherwise you will get an
error because the code is trying to name a sheet a name that already exists
with another sheet, so I have to do some rewrite of the code.
Tell me this about how you use this file. If the entries in B1:H4 are to be
sheet names, is it that you change one of the names in B1:H4? Is that what
you do? If so then do you want just the one name that you replaced changed
in the sheet that had the old name?
I will add an error trap in the code to pick up on the fact that a name in
B1:H4 is already assigned to one of the sheets and the code will not try to
rename a sheet by that name.
Write back and tell me a bit more about how you use this file as regards the
sheet names and B1:H4.
If you wish, send me your file via email and we'll work it out with emails.
My email is (e-mail address removed). Remove the "extra" from this
address. Identify yourself as NickTheBatMan so I know who you are. HTH

Nick
Here are 2 macros that must both be in your file. Between the 2 of
them, they will rename all the sheets (except Headers and Links sheets) to
the names in B1:H4 of the Headers sheet. The trigger that will fire these
macros is a change in the content of any cell in the B1:H4 range.
You have already placed the RenameExistingSheets macro correctly and I
haven't changed it from what I sent you before. But the other macro (the
first macro below) is different in that it MUST be placed in a different
kind of module. That macro is a sheet macro and MUST be placed in the
sheet
module of the Headers sheet. You can access that module by right-clicking
on the Headers sheet tab and selecting View Code. Paste that macro into
that module. "X" out of the VBE to return to your sheet. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B1:H4")) Is Nothing Then
Call RenameExistingSheets
End If
End Sub

Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub"NickTheBatMan" <[email protected]> wrote in message

On 17 Apr, 21:33, "Otto Moehrbach" <[email protected]>
wrote:

Nick
Up until now I thought I understood what you wanted. But now, I don't
know. You say:
"I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in
each
cell. That you wanted a bunch of your existing sheets named the names in
this list, one name per sheet. What do you mean by "so that I only have
to
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step
procedure
of how you would do this if you were doing it manually. Leave nothing
out!
Otto
On 17 Apr, 14:02, "Otto Moehrbach" <[email protected]>
wrote:
Nick
What do you mean by "I was hoping that it may work after I had changed
the contents of the cell and come out of it...". What cell? In what
sheet?
Do you mean you wanted the code to fire when you changed the entry in
some
cell? That's easy enough to do.
What buttons? Otto
Many thanks Otto, that works - sort of...
I was hoping that it may work after I had changed the contents of the
cell and come out of it...
The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...
Nick
On 16 Apr, 23:36, "Otto Moehrbach" <[email protected]>
wrote:
Nick
Make a copy of your file and do all this on the copy until you feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the VBE
(Visual Basic Editor). On the left side of the VBE should be a pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do
Insert -
Module. This brings up a larger blank pane on the right. Paste the
macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro. Click
on
it.
Click on Run. See what happens. If you feel shaky doing this, send
me
your
file and I'll place the macro for you and maybe give you a button in
the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this address.
HTH

On Apr 16, 8:22 pm, "Otto Moehrbach"
<[email protected]>
wrote:
Nick
This little macro will rename every sheet in the file by the list
you
have. It will not rename the "Headers" or "Links" sheets. HTH
Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub
Thanks you Otto, where do I put it ?
Just off to bed at 22:00 for the first of four 05:00 alarms for
work
!
Shall catch up tomorrow through Google Groups...
Nick- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
Hi Otto, sorry about that, getting mixed up between what I'm doing and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !
I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...
I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing ! - Hide quoted text -
- Show quoted text -

Otto, sorry for the mix up as you say I got it totally wrong in what I
was wanting...

I am hoping that when I alter any of the cells B1-H4 in this Headers
sheet, that it will name the sheets from 3 onwards - the first 2 are
set as Links & Headers. Does that explain it better ?

I think as usual my poor way of putting what I want across is letting
me down and I'll just leave it that I have to remember to change the
sheet names manually...

Nick- Hide quoted text -

- Show quoted text -

Otto, thank you.
I've been very busy today and only just got chance to have a go...

I've followed your instructions and the about which I've added into
the Module of the Headers sheet is returning a Runtime Error 1004
All I've typed in is National Lines

When I debug it highlights this
ws.Name = rSheetNames(c).Value

I wish I knew what I was doing so that I didn't have to trouble you !
Many thanks again...
Nick

Otto, very many thanks for all your time and effort with this, I see
it is massively more complex than I first realised.

As I have the sheets already named and it is going to be much more
complex to get your scripts working under the current way it all works
I shall forget the plan and just change the names manually.

Again very many thanks for your efforts in helping me with this.

Nick

 

[Otto Moehrbach]

Nick
You're welcome. Thanks for the feedback. Otto

Nick
There is a problem with the code I wrote for you. If none of your
sheets are named any of the sheet names in B1:H4, and you type in a sheet
name in that range, the code will work fine. Otherwise you will get an
error because the code is trying to name a sheet a name that already
exists
with another sheet, so I have to do some rewrite of the code.
Tell me this about how you use this file. If the entries in B1:H4 are to
be
sheet names, is it that you change one of the names in B1:H4? Is that
what
you do? If so then do you want just the one name that you replaced
changed
in the sheet that had the old name?
I will add an error trap in the code to pick up on the fact that a name
in
B1:H4 is already assigned to one of the sheets and the code will not try
to
rename a sheet by that name.
Write back and tell me a bit more about how you use this file as regards
the
sheet names and B1:H4.
If you wish, send me your file via email and we'll work it out with
emails.
My email is (e-mail address removed). Remove the "extra" from this
address. Identify yourself as NickTheBatMan so I know who you are. HTH

Nick
Here are 2 macros that must both be in your file. Between the 2 of
them, they will rename all the sheets (except Headers and Links sheets)
to
the names in B1:H4 of the Headers sheet. The trigger that will fire
these
macros is a change in the content of any cell in the B1:H4 range.
You have already placed the RenameExistingSheets macro correctly and I
haven't changed it from what I sent you before. But the other macro
(the
first macro below) is different in that it MUST be placed in a
different
kind of module. That macro is a sheet macro and MUST be placed in the
sheet
module of the Headers sheet. You can access that module by
right-clicking
on the Headers sheet tab and selecting View Code. Paste that macro into
that module. "X" out of the VBE to return to your sheet. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B1:H4")) Is Nothing Then
Call RenameExistingSheets
End If
End Sub

Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub"NickTheBatMan" <[email protected]> wrote in message

On 17 Apr, 21:33, "Otto Moehrbach" <[email protected]>
wrote:

Nick
Up until now I thought I understood what you wanted. But now, I don't
know. You say:
"I have a sheet called Headers that I'm using to enter data into
cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...".
I thought that you had a range of cells, B1:H4, with a text entry in
each
cell. That you wanted a bunch of your existing sheets named the names
in
this list, one name per sheet. What do you mean by "so that I only
have
to
alter one cell to get them all to be the same"??
Perhaps it would be better if you just write down a step-by-step
procedure
of how you would do this if you were doing it manually. Leave nothing
out!
Otto

"NickTheBatMan" <[email protected]> wrote in message

On 17 Apr, 14:02, "Otto Moehrbach" <[email protected]>
wrote:

Nick
What do you mean by "I was hoping that it may work after I had
changed
the contents of the cell and come out of it...". What cell? In what
sheet?
Do you mean you wanted the code to fire when you changed the entry
in
some
cell? That's easy enough to do.
What buttons? Otto

"NickTheBatMan" <[email protected]> wrote in message

Many thanks Otto, that works - sort of...

I was hoping that it may work after I had changed the contents of
the
cell and come out of it...

The problem I now have is that I have Run-time error '1004'
That means I have over 31 characters and there are ones that
contravene the : \ / * [ ] rule for naming the sheets...
I'll have to rethink the wording of the buttons - or just do it
manually...

Nick

On 16 Apr, 23:36, "Otto Moehrbach" <[email protected]>
wrote:

Nick
Make a copy of your file and do all this on the copy until you
feel
comfortable with it and it's doing what you want.
With your file on the screen, do Alt-F11. This takes you to the
VBE
(Visual Basic Editor). On the left side of the VBE should be a
pane
labeled
Project - VBA Project. If it's not there, click on View - Project
Explorer.
Find your file name in the Project Explorer and click on it. Do
Insert -
Module. This brings up a larger blank pane on the right. Paste
the
macro
there. "X" out of the VBE to return to your sheet.
Note that the macro name is RenameExistingSheets.
Click on Tools - Macro - Macros. Find the name of the macro.
Click
on
it.
Click on Run. See what happens. If you feel shaky doing this,
send
me
your
file and I'll place the macro for you and maybe give you a button
in
the
Header sheet that you can click on to run the macro. My email is
(e-mail address removed). Remove the "extra" from this
address.
HTH


On Apr 16, 8:22 pm, "Otto Moehrbach"
<[email protected]>
wrote:
Nick
This little macro will rename every sheet in the file by the
list
you
have. It will not rename the "Headers" or "Links" sheets. HTH
Otto
Sub RenameExistingSheets()
Dim rSheetNames As Range
Dim ws As Worksheet
Dim c As Long
Application.ScreenUpdating = False
c = 1
Sheets("Headers").Select
Set rSheetNames = Range("B1:H4")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Headers" And ws.Name <> "Links" Then
ws.Name = rSheetNames(c).Value
c = c + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub

Thanks you Otto, where do I put it ?

Just off to bed at 22:00 for the first of four 05:00 alarms for
work
!
Shall catch up tomorrow through Google Groups...
Nick- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Otto, sorry about that, getting mixed up between what I'm doing
and
trying to do... if you look back in the thread you'll see that I'm
doing this instead of doing it in Access !

I have a sheet called Headers that I'm using to enter data into cells
and using that to refer to in these other sheets so that I only have
to alter one cell to get them all to be the same - this is the same
cell that I'm hoping to use to name the different sheets...

I meant cell when I typed button, it's going to end up being a button
in a form in Access when I've finished... I'm stuck to using Excel to
prove the layout and procude another list in another sheet of this
book which I'll be using to update the Access table
eventually...................................... oh why do I let
myself in fot this sort of thing ! - Hide quoted text -

- Show quoted text -

Otto, sorry for the mix up as you say I got it totally wrong in what I
was wanting...

I am hoping that when I alter any of the cells B1-H4 in this Headers
sheet, that it will name the sheets from 3 onwards - the first 2 are
set as Links & Headers. Does that explain it better ?

I think as usual my poor way of putting what I want across is letting
me down and I'll just leave it that I have to remember to change the
sheet names manually...

Nick- Hide quoted text -

- Show quoted text -

Otto, thank you.
I've been very busy today and only just got chance to have a go...

I've followed your instructions and the about which I've added into
the Module of the Headers sheet is returning a Runtime Error 1004
All I've typed in is National Lines

When I debug it highlights this
ws.Name = rSheetNames(c).Value

I wish I knew what I was doing so that I didn't have to trouble you !
Many thanks again...
Nick

Otto, very many thanks for all your time and effort with this, I see
it is massively more complex than I first realised.

As I have the sheets already named and it is going to be much more
complex to get your scripts working under the current way it all works
I shall forget the plan and just change the names manually.

Again very many thanks for your efforts in helping me with this.

Nick