Workbook Macro
in [General]
|
Prev: Moving certain data to different sheet
Next: Sumif number is greater than a number but less than another number
From: terilad on 7 May 2010 13:26 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
From: Don Guillett on 7 May 2010 13:29 Pls do NOT post in more than ONE group. See ans in .programming -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "terilad" <terilad(a)discussions.microsoft.com> wrote in message news:BB73E42A-1ABD-479A-A5FE-A3952D13F316(a)microsoft.com... > 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
From: Jim Thomlinson on 7 May 2010 13:35 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 -- HTH... Jim Thomlinson "terilad" wrote: > 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
From: terilad on 7 May 2010 13:47 Hi Jim, Not working for me. Any ideas Mark "Jim Thomlinson" wrote: > 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 > -- > HTH... > > Jim Thomlinson > > > "terilad" wrote: > > > 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
From: Jim Thomlinson on 7 May 2010 14:21
Is the value in cell A1 a formula? If so where is the precident cell to the formula? -- HTH... Jim Thomlinson "terilad" wrote: > Hi Jim, > > Not working for me. Any ideas > > Mark > > "Jim Thomlinson" wrote: > > > 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 > > -- > > HTH... > > > > Jim Thomlinson > > > > > > "terilad" wrote: > > > > > 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 |