#1
|
|||
|
|||
Variable to rename a tab in a worksheet via VBA
Hi
I'm trying to rename a worksheet tab via VBA. My problem is that the tab has a changing name. EG Sheets("Sheet1").Select Sheets("Sheet1").Name = "File 1" "Sheet1" in this case will change in name next time I want to run the macro , so it will fail. How can I build in a variable for the sheet selection so I can always rename it 'File 1' , whatever it used to be. It's always and only the first sheet I'm applying this to , by the way. Grateful for any help. |
#2
|
||||
|
||||
If the sheet will always be in the same position then you can use its Index instead of its Name.
eg Code:
Sheets(1).Select eg Code:
Sheet1.Select https://www.msofficeforums.com/excel...html#post36290 |
#3
|
||||
|
||||
I'd prefer to work with sheet names. That way, if someone re-orders the worksheets, the code won't try to process the wrong one. To ensure the code always processes a sheet named "File 1", even if that sheet starts out named "Sheet1", you could use code like:
Code:
Sub Demo() Dim xlWs As Worksheet, bFound1 As Boolean, bFound2 As Boolean With ThisWorkbook bFound1 = False For Each xlWs In .Worksheets With xlWs If .Name = "File 1" Then bFound1 = True MsgBox .Range("A1").Value Exit For ElseIf .Name = "Sheet1" Then bFound2 = True End If End With Next If bFound1 = False And bFound2 = True Then .Worksheets("Sheet1").Name = "File 1" MsgBox .Worksheets("File 1").Range("A1").Value End If End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
||||
|
||||
Quote:
Quote:
However, the fact that the user can change the both the sheet's order and name (if workbook structure is left unprotected) is why I suggested using the sheet's codename instead, because that cannot be changed by the user unless the user goes into the VBA IDE. This would be my preference. |
#5
|
||||
|
||||
Hi Colin,
I don't have any issues with that - as far as it does. IMHO, though, it's not good to pre-suppose the required sheet will always be the first sheet and, given that the desired sheet might have one of two names, positively identiying the sheet via the name is the better approach.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
||||
|
||||
Hi Paul,
I'll leave it to the OP to validate his/her assumption that the target sheet will always be the first sheet. I realised I forgot to post some sample Codename code for the OP, which is the approach I favour. Code:
Sub demo() Const strNEW_NAME As String = "File 1" 'a workbook cannot have sheets with duplicate names If Not SheetExists(ThisWorkbook, strNEW_NAME) Then 'sheet1 is the codename of the sheet, determined at design time Sheet1.Name = strNEW_NAME End If End Sub 'a generic function to check if a sheet exists Private Function SheetExists(ByVal wkbTarget As Workbook, ByRef strName As String) On Error Resume Next SheetExists = Not wkbTarget.Sheets(strName) Is Nothing End Function |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to Rename a Quick Part | sleake | Word | 14 | 10-14-2013 09:21 AM |
Run-time error '91': Object variable or With block variable not set | tinfanide | Excel Programming | 2 | 06-10-2012 10:17 AM |
Rename Files | gsrikanth | Excel Programming | 3 | 05-14-2012 03:03 AM |
"Auto-populating" data-worksheet to worksheet. | meggenm | Excel | 4 | 02-04-2012 02:04 AM |
Rename File | cksm4 | Word VBA | 2 | 02-25-2011 09:29 AM |