Error with VBA for naming sheet from cell

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
655
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If you want the macro to run each time the workbook is opened, use
VBA Code:
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

Well-known Member
Joined
Jan 4, 2008
Messages
655
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Which cell on which sheet will be the trigger. ??
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

You could run it from the Open event, say every 10 seconds
This in the This Workbook module
VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,860
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
655
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
IS it the same cell on ALL sheets ??
How many sheets are involved ?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,860
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,148,422
Messages
5,746,589
Members
424,032
Latest member
pochie2741

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top