Macro needs to rename worksheet tab name

[Monomeeth]

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.

 

[OssieMac]

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

 

[Normek]

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