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

--
crowdx4
-----------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774
View this thread: http://www.excelforum.com/showthread.php?threadid=57365

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

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

"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

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

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

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


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

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


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

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


--
Regards,
Norman