Copy worksheet name into cell A1?

[crowdx42]

Ok, so I just want to copy the name from a worksheet into the cell A1,
need this to work relative across 20 worksheets. Also in the same macr
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 .xl
at the end of the name.
Any help gratefully appreciated.
Patric

 

[Norman Jones]

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
'<<=============

 

[NickHK]

One way is with a UDF:
Public Function WSName(AnyCell As Range) As String
With Application
.Volatile
WSName = .Caller.Parent.Name
End With
End Function

As for the name change:
With Worksheets(x)
.Name = Left(.Name, Len(.Name) - 4)
End With

NickHK

 

[NickHK]

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]

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.

 

[Norman Jones]

Hi Nick,

Just to add and to guess at a possible scenario, the OP may have named the
sheets in a fashion resembling:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim i As Long
Const myPrefix As String = "ABC"

For Each SH In ActiveWorkbook.Worksheets
i = i + 1
SH.Name = i & ActiveWorkbook.Name
Next SH

End Sub
'<<=============

 

[crowdx42]

Ok, just to clarify. The original sheets were opened renamed from their
file name and then moved into a master workbook. This is how each sheet
has a name with .xls in it.
How does the UDF code work? Is it inserted in the view code on the
individual sheet tab? I have tried this and it did not work?

Help !
Patrick

 

[Norman Jones]

Hi Patrick,

Ok, just to clarify. The original sheets were opened renamed from their
file name and then moved into a master workbook. This is how each sheet
has a name with .xls in it.
How does the UDF code work? Is it inserted in the view code on the
individual sheet tab? I have tried this and it did not work?

Copy the code into a standard module:
Alt-F11 to open the VBE
Insert Module
Paste the suggested code.

To run the macro from Excel:
Alt-F8 to open the Macro dialog
Select 'Tester' (or your replacement name)
Run