Renaming Multiple Tabs

[Pete122]

I have hundreds of tabs in an excel file all labeled with #1 and so on. How would I go about renaming all the tabs in one go to just getting them numbered such as 1, 2, 3, ...

 

[Fluff]

Hi & welcome to the board.
How about

Sub renameTabs()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Left(Ws.Name, 1) = "#" Then Ws.Name = Replace(Ws.Name, "#", "")
   Next Ws
End Sub

 

[Pete122]

Hi & welcome to the board.
How about

Sub renameTabs()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Left(Ws.Name, 1) = "#" Then Ws.Name = Replace(Ws.Name, "#", "")
   Next Ws
End Sub

It worked beautifully. Thanks for the help

 

[Fluff]

Glad to help & thanks for the feedback

 

[Pete122]

Another question since you were able to help me out big time. I'm running into a problem when I try to reference the same cell, but in different sheets. Whenever I type the equation for the reference, =1!$A$1, I drag it down to repeat the process so that it does the 2nd sheet and so on, but excel automatically adds single quotes around the 1 and locks the 1 in the equation causing it to repeat the equation with no change. What can I do to fix this issue? I copied and pasted my excel sheet below because the forum wont let me upload my file. (The other sheets are labeled as 1,2,3,4,5,6,7,8,9).
[TABLE="width: 144"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]true/false[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]='1'!$A$1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Not sure you can "fix it", because the sheet name is numerical it needs to be enclosed it in single quotes.