Workbook Macro

T

terilad

Hi,

How can I write this code to place in the workbook of the file, the macro
changes the sheet tab name as per the individual sheet cell A1 which is
changed from the first sheet Index of Stock, I am looking to do this so it
changes instantly when the name is changed and do not need to calculate each
sheet, I have over 100 sheets.

Here is the macro

Private Sub Worksheet_Calculate()
With Me.Range("A1")
If .Value <> "" Then
Me.Name = .Value
End If
End With
End Sub

Many thanks

Mark
 
J

Jim Thomlinson

Try this code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Me.Name = Target.Value
End If
End Sub
 
T

terilad

Hi Jim,

Not working for me. Any ideas

Mark

Jim Thomlinson said:
Try this code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Me.Name = Target.Value
End If
End Sub
 
J

Jim Thomlinson

Is the value in cell A1 a formula? If so where is the precident cell to the
formula?
 
T

terilad

Hi Jim,

yes it is a formula, it is ='Index of Stock'!A3 and so on for all the sheets
so sheet 2 is ='Index of Stock'!A4 and so on for 100.

Regards

Mark
 
J

Jim Thomlinson

Do you have an adequate answer in programming? Do we need to continue this
thread? You need to pick one thread to continue with...
 
T

terilad

Hi Jim,

No adequate answer in programming, it doesn't work the suggestion that was
in programming.

Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top