If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Workbook Macro
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 |
#2
|
|||
|
|||
Workbook Macro
Pls do NOT post in more than ONE group. See ans in .programming
-- Don Guillett Microsoft MVP Excel SalesAid Software "terilad" wrote in message ... 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 |
#3
|
|||
|
|||
Workbook Macro
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 |
#4
|
|||
|
|||
Workbook Macro
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 |
#5
|
|||
|
|||
Workbook Macro
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 |
#6
|
|||
|
|||
Workbook Macro
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 "Jim Thomlinson" wrote: 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 |
#7
|
|||
|
|||
Workbook Macro
Do you have an adequate answer in programming? Do we need to continue this
thread? You need to pick one thread to continue with... -- HTH... Jim Thomlinson "terilad" wrote: 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 "Jim Thomlinson" wrote: 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 |
#8
|
|||
|
|||
Workbook Macro
Hi Jim,
No adequate answer in programming, it doesn't work the suggestion that was in programming. Mark "Jim Thomlinson" wrote: Do you have an adequate answer in programming? Do we need to continue this thread? You need to pick one thread to continue with... -- HTH... Jim Thomlinson "terilad" wrote: 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 "Jim Thomlinson" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|