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]

I'm back and while i thanked i just assumed it would work when i got the time to try it. i,ve now tried and its seemingly not working. in my ultra novice nosey at the code i see "G" listed as the monitor column when my data is in H. i did have a quick edit of every ref to G to H but code still didn't work.
i also tried a new workbook with this code and even data change in column G didn't rename sheet tab name

 

[Michael M]

Maybe this way

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

 

[leecavturbo]

and to correct myself column G was my request so apologies. my data column moved to H in a later iteration.

Michael M i didn't thank you for your previous input and now super speedy response. i have just trialled your code but im not getting any refresh. what is this location referring to? row 10 column 8 and 31? "Name = Left(.Cells(10, 8).Value, 31)"

 

[Michael M]

Cells(10, 8)

Refers to row 10 and column "H" in each and every sheet.
As for auto running the code, you could make it auto_open event so that whenn the workbook is opened , it runs

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

This goes in the "This Workbook" module

 

[leecavturbo]

could we make it run anytime data is changed in column H?

 

[Michael M]

You would have to put it in each sheet module that you want it to run on ??
AND
IIt would need to be a worksheet_Chnage event

 

[leecavturbo]

You would have to put it in each sheet module that you want it to run on ??
AND
IIt would need to be a worksheet_Chnage event

heavy going ? 200 sheets

 

[Michael M]

sounds like post #32 is the way to go...make ALL the changes in the workbook THEN run the code !

 

[Peter_SSs]

You would have to put it in each sheet module that you want it to run on ??

Couldn't it go once in the ThisWorkbook module like your earlier code?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  With Sh
    If Not Intersect(Target, .Columns(8)) Is Nothing Then
      On Error Resume Next
      .Name = Left(.Cells(10, 8).Value, 31)
      On Error GoTo 0
    End If
  End With
End Sub

@leecavturbo
You might need something to do all the sheets initially but the the above should do any renaming when any cell in column H is changed (not by formula though).