Private Sub Workbook

T

terilad

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
 
D

Don Guillett

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
 

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