Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make the sheet index name same as the sheet name after inserting sheets?
When a new worksheet is added to a workbook then Sheet4 takes the
Sheet3's index name and becomes Sheet3(Sheet4) and so on. I am referencing the new worksheets using Sheets(i).Select. I want Sheet4 to be selected but inserting worksheets makes Sheet3 as Sheet4. So, I am stuck here Please let me know How to make the sheet index name same as the sheet name after inserting sheets? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make the sheet index name same as the sheet name after inse
The sheets always reference from the left hand side of the tabs when using
either:- For each ws in worksheets or For i = 1 to worksheets.count However, the worksheet CodeName does not change. The CodeName is the name to the left and not in brackets you see in the project explorer in the VBA Editor. Looks something like this:- Sheet1(MySheet) Sheet2(Sheet2) Sheet3(Sheet3) The name in brackets is the one that you can change. The other name remains as is and you can use it to reference a specific worksheet like this:- Sheet1.Range("A1") in lieu of Sheets("MySheet").Range("A1") Irrespective of user changes to the worksheet name, the code name remains constant. I realise that you might still have a problem when trying to loop through the worksheets but you can still test for the code name of a worksheet like this example:- For i = 1 To Worksheets.Count If Worksheets(i).CodeName = "Sheet2" Then MsgBox "Correct Sheet2" Else MsgBox "Not correct sheet" End If Next i Not exactly the answer you would like but hope it helps anyway. Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make the sheet index name same as the sheet name after inse
On Oct 26, 12:49 am, OssieMac
wrote: The sheets always reference from the left hand side of the tabs when using either:- For each ws in worksheets or For i = 1 to worksheets.count However, the worksheet CodeName does not change. The CodeName is the name to the left and not in brackets you see in the project explorer in the VBA Editor. Looks something like this:- Sheet1(MySheet) Sheet2(Sheet2) Sheet3(Sheet3) The name in brackets is the one that you can change. The other name remains as is and you can use it to reference a specific worksheet like this:- Sheet1.Range("A1") in lieu of Sheets("MySheet").Range("A1") Irrespective of user changes to the worksheet name, the code name remains constant. I realise that you might still have a problem when trying to loop through the worksheets but you can still test for the code name of a worksheet like this example:- For i = 1 To Worksheets.Count If Worksheets(i).CodeName = "Sheet2" Then MsgBox "Correct Sheet2" Else MsgBox "Not correct sheet" End If Next i Not exactly the answer you would like but hope it helps anyway. Regards, OssieMac OssieMac, Ws.codename was the key here and I got that from your code. Also, I understood your explanation. Thanks for the detailed clarification. I am now using Sub aa() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.CodeName = "Sheet4" Then ws.Select Range("a1").Value = 100 'Else ' MsgBox "Not correct sheet" End If Next ws End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to have an index sheet but all other sheets should be hidden | Excel Worksheet Functions | |||
how do I apply changes I make on one sheet to other sheets? | Excel Discussion (Misc queries) | |||
Activitate sheet 1 and make all sheets read-only | Excel Programming | |||
Activitate sheet 1 and make all sheets read-only | Excel Programming | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming |