Prev: How to run a macro if I don't know the name in advance
Next: Workbook Macro
From: terilad on 7 May 2010 12:51
Hi,

I was wondering how I can place the following macro into the workbook rather
than having to inpu this code into over 100 individual sheets, the code
changes the sheet tab name to that of cell A1 per every sheet, these are
changed from the first sheet called Index of Stock, so when the name is
changed in the index of stock it changes instantly on the individual sheet
and the sheet tab. Here's the code.

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:05
Place in the ThisWorkbook module. Either of the below should work

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)

If Target.Address <> Range("a1").Address Or _
Target = "" Then Exit Sub

Sh.Name = Target

'With Sh.Range("A1")
' If .Value <> "" Then Sh.Name = .Value
'End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"terilad" <terilad(a)discussions.microsoft.com> wrote in message
news:C12A1BE1-5C3E-4863-8F3A-D05C8E036B05(a)microsoft.com...
> Hi,
>
> I was wondering how I can place the following macro into the workbook
> rather
> than having to inpu this code into over 100 individual sheets, the code
> changes the sheet tab name to that of cell A1 per every sheet, these are
> changed from the first sheet called Index of Stock, so when the name is
> changed in the index of stock it changes instantly on the individual sheet
> and the sheet tab. Here's the code.
>
> Private Sub Worksheet_Calculate()
> With Me.Range("A1")
> If .Value <> "" Then
> Me.Name = .Value
> End If
> End With
> End Sub
>
>
> Many thanks
>
> Mark

 | 
Pages: 1
Prev: How to run a macro if I don't know the name in advance
Next: Workbook Macro