Auto Complete Name

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
 
B

Bob Phillips

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.
 
G

grumblenoise

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 :)
 
B

Bob Phillips

If you put it in the correct place, changing B1 on the master sheet will
trigger the event macro, it all happens silently <g>
 
N

Nayab

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
 
G

grumblenoise

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.
 
G

grumblenoise

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 :)
 
C

Cimjet

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
 
G

grumblenoise

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!!!!!! :)
 
Top