Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs to rename worksheet tab name
Hi Everyone
I've recorded/coded a macro for use by various people in our organisation in order to filter some data and then randomly select 20% of the remaining rows. However, in order for this to work, I need the macro to rename the first worksheet tab. My problem is that this macro is designed to run on various reports and as a result, the first worksheet tab name will be different in each workbook. Is there some way for a macro to just select whatever worksheet is the first one appearing in the row of tabs at the bottom and regardless of what it is called, to rename it to something else? If it helps, I'm pretty sure in all instances these workbooks only have the one worksheet. However, if that isn't a factor, I imagine whatever code would work could be adapted to select any worksheet based on its order in the row of worksheet tabs at the bottom. Any help would be greatly appreciated. Thanks! Joe. -- If you can measure it, you can improve it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs to rename worksheet tab name
Hi Joe,
Try the following. Note the error trapping is essential. However, if re-naming the first sheet with the same name then it does not produce an error. Sub ReNameWorksheet() Dim strShtname As String strShtname = "My New Sht Name" On Error Resume Next ThisWorkbook.Sheets(1).Name = strShtname If Err.Number 0 Then MsgBox "Cannot re-name worksheet." & vbLf _ & strShtname & " already exists." End If On Error GoTo 0 End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs to rename worksheet tab name
Hi Joe,
Sheets(1).Select 'Go to the first sheet Range("a1").Parent.Name = "My sheet name" 'The range address is unimportant Alternatively you can find the name of the first sheet and put it into a variable and use that variable SheetName = Format(Range("a1").Parent.Name, "mm") 'Gets the sheet name or just SheetName = Range("a1").Parent.Name -- Normek "Monomeeth" wrote: Hi Everyone I've recorded/coded a macro for use by various people in our organisation in order to filter some data and then randomly select 20% of the remaining rows. However, in order for this to work, I need the macro to rename the first worksheet tab. My problem is that this macro is designed to run on various reports and as a result, the first worksheet tab name will be different in each workbook. Is there some way for a macro to just select whatever worksheet is the first one appearing in the row of tabs at the bottom and regardless of what it is called, to rename it to something else? If it helps, I'm pretty sure in all instances these workbooks only have the one worksheet. However, if that isn't a factor, I imagine whatever code would work could be adapted to select any worksheet based on its order in the row of worksheet tabs at the bottom. Any help would be greatly appreciated. Thanks! Joe. -- If you can measure it, you can improve it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename Worksheet Tab as File Name Using a Macro | Excel Discussion (Misc queries) | |||
Macro: Copy a worksheet and rename it to a specified value | Excel Discussion (Misc queries) | |||
Rename worksheet in macro | Excel Worksheet Functions | |||
new worksheet and rename from cell contents macro | Excel Worksheet Functions | |||
Rename a Worksheet within a Macro | Excel Programming |