Activesheet Rename

[John Davis]

Hello All:

How would I code the Active Sheet to be renamed Sheet1?

 

[VBA Noob]

Try

ActiveSheet.Name = "Sheet1"

This would check if Sheet1 already exists

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

 

[John Davis]

Try

ActiveSheet.Name = "Sheet1"

VBA Noob

Thanks alot.

 

[lenze]

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

 

[John Davis]

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.

 

[Norie]

John

Why hardcode the sheet name?

 

[lenze]

Yes, but a better approach might be to use the Index Name in your code. So instead of

Worksheets("Sheet1").Range.......

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

 

[Norie]

lenze

Surely rather than hardcoding it would be better to use something like this.

Set wsFirst = Worksheets(1)

 

[John Davis]

John

Why hardcode the sheet name?

I thought it was needed since that's how my Macro's identifies the Sheet

For example:

Set Sh = Worksheets("Sheet1")

 

[Norie]

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.