Hi Nick,

That's what I thought first, but it is not possible to have the 20 sheets
named after the WB, as they would conflict.

My suggested code changes the existing sheet names and merely shortens
previously valid names.


---
Regards,
Norman



"NickHK" wrote in message
...
Norman,
That's what I thought first, but it is not possible to have the 20 sheets
named after the WB, as they would conflict.
So maybe 1 sheet is called that but not the other 19. In which case, you
only need to rename 1 sheet.
But depends what the OP means...

NickHK

"Norman Jones" wrote in message
...
Hi Patrick,

Try something like:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE

For Each SH In WB.Worksheets
With SH
.Name = Left(.Name, Len(.Name) - 4)
.Range("A1").Value = .Name
End With
Next SH
End Sub
'<<=============


---
Regards,
Norman


"crowdx42" wrote
in
message ...

Ok, so I just want to copy the name from a worksheet into the cell A1,
I
need this to work relative across 20 worksheets. Also in the same macro
is it possible to delete the last 4 characters in the worksheet name?
The worksheet was originally named from the file name and so has .xls
at the end of the name.
Any help gratefully appreciated.
Patrick


--
crowdx42
------------------------------------------------------------------------
crowdx42's Profile:
http://www.excelforum.com/member.php...o&userid=37749
View this thread:
http://www.excelforum.com/showthread...hreadid=573657