Please can you help me debug this code?

lucie19

New Member
Joined
Apr 28, 2014
Messages
6
Hi All

I'm trying to get a button to apply the contents of A1 to the tab name on every sheet, (if that makes sense?)

I was using this code successfully on some other similar spreadsheets but now it simply won't work. I am getting this error "Run-time error '1004': Method 'Name' of object' _Worksheet' failed

The code I am using is below.

Thanks as always for your help.

Sub myTabNames()
Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
ws.Name = ws.Range("A1").Value
Next
End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
What do you mean "the contents of A1 to the tab name on every sheet".
The code, as written, tries to make the tab name of every sheet the same as the value of range("A1") of that sheet.
The error sounds like either there is an illegal sheet name in A1 of some sheet (like a blank cell), or that there may be a duplicate value in A1.
 

lucie19

New Member
Joined
Apr 28, 2014
Messages
6
Yes that is what I'm trying to do but I think I've got it now. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top