Auto Complete Name

  • Thread starter Thread starter grumblenoise
  • Start date Start date
G

grumblenoise

Hi Guys,

I have an excel document with a number of worksheets, in cell B1 there
is a name cell, I want to copy this name cell to all the other
worksheets so each sheet B1 has the name. How this best achieved?

Cheers,

Grumbz
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1"
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

For Each sh In ThisWorkbook.Worksheets

If sh.Name <> Me.Name Then

sh.Range(WS_RANGE).Value = .Value
End If
Next sh
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.
 
Thanks, that code looks like it does the job. Now, forgive me but
when I am in the VB editor, how do I get it to run, I don't often
dabble in here so I'm a little unsure.

Thanks for your help, it's much appreciated :)
 
If you put it in the correct place, changing B1 on the master sheet will
trigger the event macro, it all happens silently <g>
 
Thanks, that code looks like it does the job.  Now, forgive me but
when I am in the VB editor, how do I get it to run, I don't often
dabble in here so I'm a little unsure.

Thanks for your help, it's much appreciated :)
Click to expand...

u can put the cursor within the module and then press F5 or click the
small run macro button
 
Hmmm, can;t get it working. I'll tell you what I did.

Created a new document, on Sheet 1, right clicked and chose view code,
then I pasted in the code in to the VB editor. THen I went to Sheet
1, put my name in and that's where it stops. Is there something I am
missing?

Thanks.
 
Yep, B1, Sheet 1. Right clicked on the Sheet 1 tab at the bottom and
pasted my code in to their, it prompts me to save it when I run it,
which I did.

Thanks again for helping me :)
 
One more way you can do it is to hold the <Ctrl> key and select all your
worksheet, now when to type on your master sheet it will copy on all the
selected sheets.
HTH
 
Got it working mate! Thanks a lot, I was trying to run it on B2!
Oopps, once I was in B1 it was perfect.

Thanks very very much indeed!!!!!! :-)
 
Back
Top