Activesheet Rename

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try

Code:
ActiveSheet.Name = "Sheet1"

This would check if Sheet1 already exists

Code:
On Error GoTo Trap
ActiveSheet.Name = "Sheet1"

Trap:
    If Err.Number = 1004 Then
        MsgBox "There is already a sheet called Sheet1!!"
    End If

VBA Noob
 
Last edited:
Upvote 0
That will work, but you need to be careful

Each sheet has 2 Names, The name on the Sheet Tab(Which is what you are changing with this code) and The Index Name. In a new workbook, you will have a sheet with Index Name and Sheet Name both "Sheet1". You can, using the code, change another sheet's Tab Name to "Sheet1", provided you have renamed the original "Sheet1"

So you could have 2 sheets, one with an Index Name of "Sheet1" and a Tab Name of "Sheet2" while the other might have an Index Name of 'Sheet2" and a tab name of "Sheet1"

I actually saw Bill (AKA MrExcel use this once).

The only reason I mention this is because it could become very confusing if you are trying to write VBA for the workbook

lenze
 
Upvote 0
That will work, but you need to be careful

Each sheet has 2 Names, The name on the Sheet Tab(Which is what you are changing with this code) and The Index Name. In a new workbook, you will have a sheet with Index Name and Sheet Name both "Sheet1". You can, using the code, change another sheet's Tab Name to "Sheet1", provided you have renamed the original "Sheet1"

So you could have 2 sheets, one with an Index Name of "Sheet1" and a Tab Name of "Sheet2" while the other might have an Index Name of 'Sheet2" and a tab name of "Sheet1"

I actually saw Bill (AKA MrExcel use this once).

The only reason I mention this is because it could become very confusing if you are trying to write VBA for the workbook

lenze

Lenze thanks for the reply. I have a workbook forwarded too me in which the sheet tab name has been changed from Sheet1 to something else. However, whenever I open it the first sheet is the one which has the data I need to perform my tasks. Since my code identifies this sheet as Sheet1 I'm using the code too rename the sheet to Sheet1. Do you think the code above will do the trick? It seems to be working.
 
Upvote 0
John

Why hardcode the sheet name?
 
Upvote 0
Yes, but a better approach might be to use the Index Name in your code. So instead of
Code:
Worksheets("Sheet1").Range.......
Code:
use
Sheet1.Range......

The 1st sheet in the workbook should have the Index Name of "Sheet1"
Using the Index Name, it doesn't matter want the Tab Name is.

lenze
 
Upvote 0
lenze

Surely rather than hardcoding it would be better to use something like this.
Code:
Set wsFirst = Worksheets(1)
 
Upvote 0
John

There are various ways to refer to sheets.

1 Code name.

2 Name.

3 Index.

The code I posted use 1 to identify the first worksheet.

So you should be able to use that in the code if the worksheet truly is the first worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top