Re: How to name a sheet from text in a cell in another sheet ? by NickTheBatMan
NickTheBatMan
Sun Apr 20 11:20:04 CDT 2008
On 18 Apr, 16:28, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> Nick
> =A0 =A0 Here are 2 macros that must both be in your file. =A0Between 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. =A0The trigger that will fire the=
se
> 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. =A0But the other macro (th=
e
> first macro below) is different in that it MUST be placed in a different
> kind of module. =A0That macro is a sheet macro and MUST be placed in the s=
heet
> module of the Headers sheet. =A0You can access that module by right-clicki=
ng
> on the Headers sheet tab and selecting View Code. =A0Paste that macro into=
> that module. =A0"X" out of the VBE to return to your sheet. =A0Otto
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 =A0 =A0 If Target.Count > 1 Then Exit Sub
> =A0 =A0 =A0 If IsEmpty(Target.Value) Then Exit Sub
> =A0 =A0 =A0 If Not Intersect(Target, Range("B1:H4")) Is Nothing Then
> =A0 =A0 =A0 =A0 =A0 =A0 Call RenameExistingSheets
> =A0 =A0 =A0 End If
> End Sub
>
> Sub RenameExistingSheets()
> =A0 =A0 =A0 Dim rSheetNames As Range
> =A0 =A0 =A0 Dim ws As Worksheet
> =A0 =A0 =A0 Dim c As Long
> =A0 =A0 =A0 Application.ScreenUpdating =3D False
> =A0 =A0 =A0 c =3D 1
> =A0 =A0 =A0 Sheets("Headers").Select
> =A0 =A0 =A0 Set rSheetNames =3D Range("B1:H4")
> =A0 =A0 =A0 For Each ws In ActiveWorkbook.Worksheets
> =A0 =A0 =A0 =A0 =A0 =A0 If ws.Name <> "Headers" And ws.Name <> "Links" The=
n
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ws.Name =3D rSheetNames(c).Value
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 c =3D c + 1
> =A0 =A0 =A0 =A0 =A0 =A0 End If
> =A0 =A0 =A0 Next ws
> =A0 =A0 =A0 Application.ScreenUpdating =3D True
> End Sub"NickTheBatMan" <nickw7...@gmail.com> wrote in message
>
> news:da852f6c-d848-437b-94e0-4cfb38639fa5@f63g2000hsf.googlegroups.com...
> On 17 Apr, 21:33, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> 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 eac=
h
> > 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 procedu=
re
> > of how you would do this if you were doing it manually. Leave nothing ou=
t!
> > Otto
>
> > "NickTheBatMan" <nickw7...@gmail.com> wrote in message
>
> >news:2321560a-1e8c-4783-8ef6-d02cc56a4709@y21g2000hsf.googlegroups.com...=
> > On 17 Apr, 14:02, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> > 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" <nickw7...@gmail.com> wrote in message
>
> > >news:c4bd6aab-7581-4f6f-8b6e-d825359de93c@l64g2000hse.googlegroups.com.=
..
> > > 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" <moehrbachoex...@bellsouth.net>
> > > 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 o=
n
> > > > it.
> > > > Click on Run. See what happens. If you feel shaky doing this, send m=
e
> > > > 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
> > > > moehrbachoex...@bellsouth.net. Remove the "extra" from this address.=
> > > > HTH
> > > > Otto"NickTheBatMan" <nickw7...@gmail.com> wrote in message
>
> > > >news:75e8a73f-01ac-4eaf-85c2-8bc1c959d527@8g2000hsu.googlegroups.com.=
..
>
> > > > > On Apr 16, 8:22 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.ne=
t>
> > > > > 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 Ott=
o
> > > > >> Sub RenameExistingSheets()
> > > > >> Dim rSheetNames As Range
> > > > >> Dim ws As Worksheet
> > > > >> Dim c As Long
> > > > >> Application.ScreenUpdating =3D False
> > > > >> c =3D 1
> > > > >> Sheets("Headers").Select
> > > > >> Set rSheetNames =3D Range("B1:H4")
> > > > >> For Each ws In ActiveWorkbook.Worksheets
> > > > >> If ws.Name <> "Headers" And ws.Name <> "Links" Then
> > > > >> ws.Name =3D rSheetNames(c).Value
> > > > >> c =3D c + 1
> > > > >> End If
> > > > >> Next ws
> > > > >> Application.ScreenUpdating =3D 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 wo=
rk
> > > > > !
> > > > > 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 =3D rSheetNames(c).Value
I wish I knew what I was doing so that I didn't have to trouble you !
Many thanks again... :)
Nick