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

 

[leecavturbo]

but what i have found is if i have created a sheet in readiness for naming but the data isn't in the cell to name that sheet it will use the row before that does have data. it corrects if i add the data run the macro again

 

[leecavturbo]

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

yes same cell. 200-400

 

[johnnyL]

Ok, so if your designated cell is A10, when that cell is changed on sheet20, you want sheet20 name to change to what A10 was changed to or do you want something to happen to all the 200-400 sheet names?

 

[leecavturbo]

Ok, so if your designated cell is A10, when that cell is changed on sheet20, you want sheet20 name to change to what A10 was changed to or do you want something to happen to all the 200-400 sheet names?

Just sheet 20

 

[johnnyL]

Ahhhhhhhhhhhhhhhhhhhhhhhh. That makes far more sense.

 

[leecavturbo]

so if i changed mark to shaun sheet 3 name would change to shaun

 

[johnnyL]

I would suggest a worksheet change event to handle that. Unfortunately I can't think of an easy way to implement that for 200-400 pages at the moment.

 

[leecavturbo]

I would suggest a worksheet change event to handle that. Unfortunately I can't think of an easy way to implement that for 200-400 pages at the moment.

i would suggest just run macro every second ? it executes extremely fast even doing 200+sheets

 

[Michael M]

@leecavturbo

i would suggest just run macro every second ? it executes extremely fast even doing 200+sheets

As in post #5 ??

 

[johnnyL]

ahhh. Another curveball detected.

So the G column on the 'Sat' sheet is where you want the changes to be detected? Example if G5 is changed from 'mark' to 'shaun', Sheet3 should be renamed to 'shaun'?