Error with VBA for naming sheet from cell

[leecavturbo]

I have a pretty standard VBA worksheet name from cell data. but every 7th worksheet or duplicate data the worksheet name references incorrect cell or even sheet!
any ideas ?
Sub tabname()
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
ws.Name = Left(ws.Cells(10, 1).Value, 31)
Next
On Error GoTo 0
End Sub

also how can this macro auto execute ? i.e i have to run this manually currently

 

[Michael M]

If you want the macro to run each time the workbook is opened, use

Sub workbook_open()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
With ws
    .Name = Left(.Cells(10, 1).Value, 31)
End With
Next
End Sub

but that seems a bit pointless unless the data in the cells changes each time as well.

 

[leecavturbo]

I want the macro to run dynamically so if I enter data in a cell while workbook is open the worksheets are updated on the fly. Or I.e the macro runs every 1 sec eg

 

[Michael M]

Which cell on which sheet will be the trigger. ??

 

[Michael M]

You could run it from the Open event, say every 10 seconds
This in the This Workbook module

Sub workbook_open()
Application.OnTime Now + TimeValue("00:00:10"), "shtnames"
End Sub
Sub shtnames()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
With ws
    .Name = Left(.Cells(10, 1).Value, 31)
End With
Next
End Sub

Seems a bit pointless though, if sheetnames aren't affected regularly

 

[johnnyL]

I am so confused. Why the want to change all of the sheet names on a set interval and what would cause all of the sheet names to be changed? What name are you wanting to change all of the sheet names to?

 

[Michael M]

@johnnyL
Me too...it doesn't make sense to do this....but there may be good reasons for it...?

 

[leecavturbo]

I am so confused. Why the want to change all of the sheet names on a set interval and what would cause all of the sheet names to be changed? What name are you wanting to change all of the sheet names to?

if i add data to a cell that should be the sheet name. execute . ?

 

[Michael M]

IS it the same cell on ALL sheets ??
How many sheets are involved ?

 

[johnnyL]

if i add data to a cell that should be the sheet name. execute . ?

It sounds like you want a designated cell in a worksheet to be the new name of that worksheet. Not all worksheets, correct?