Copy worksheet name into cell A1?

[crowdx42]

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

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


---
Regards,
Norman


"crowdx42" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

"crowdx42" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > 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 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.


---
Regards,
Norman



"NickHK" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote
> in
>> message news:[email protected]...
>> >
>> > 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 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
'<<=============


---
Regards,
Norman



"NickHK" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote
> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

[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.
Duh, just tried the original code with the workbook name change.... worked like a charm... EXCELLENT Stuff


Patrick