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!

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

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!