Renaming Worksheets

[tcgaines]

After combining worksheets from different workbooks, the name of the worksheets, by default, is the name of the workbook, ie: products.xls. There are many worksheets. I want to implement a loop that will will remove ".xls" from each of the worksheet names. any thoughts?

Thanks.

[Ron de Bruin]

Use the left function to remove the last four

ActiveSheet.Name = Left(mybook.Name, Len(mybook.Name) - 4)



--
Regards Ron de Bruin
http://www.rondebruin.nl


"tcgaines" <[email protected]> wrote in message
news:[email protected]...
>
> After combining worksheets from different workbooks, the name of the
> worksheets, by default, is the name of the workbook, ie: products.xls.
> There are many worksheets. I want to implement a loop that will will
> remove ".xls" from each of the worksheet names. any thoughts?
>
> Thanks.
>
>
> --
> tcgaines
> ------------------------------------------------------------------------
> tcgaines's Profile: http://www.excelforum.com/member.php...o&userid=29608
> View this thread: http://www.excelforum.com/showthread...hreadid=493879
>

[Norman Jones]

Hi TC,

This is not default behaviour but is, I suspect, caused by the code used to
combine the workbooks.

Try:

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

Set WB = ActiveWorkbook '<<==== CHANGE

For Each sh In WB.Worksheets
If LCase(Right(sh.Name, 4)) = ".xls" Then
sh.Name = Left(sh.Name, Len(sh.Name) - 4)
End If
Next

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


---
Regards,
Norman


"tcgaines" <[email protected]> wrote in
message news:[email protected]...
>
> After combining worksheets from different workbooks, the name of the
> worksheets, by default, is the name of the workbook, ie: products.xls.
> There are many worksheets. I want to implement a loop that will will
> remove ".xls" from each of the worksheet names. any thoughts?
>
> Thanks.
>
>
> --
> tcgaines
> ------------------------------------------------------------------------
> tcgaines's Profile:
> http://www.excelforum.com/member.php...o&userid=29608
> View this thread: http://www.excelforum.com/showthread...hreadid=493879
>

[Lonnie M.]

Give the following a try:

Sub renameWS()
Dim ws As Worksheet
For Each ws In Worksheets
If Right(CStr(ws.Name), 4) = ".xls" Then
ws.Name = Left(CStr(ws.Name), Len(ws.Name) - 4)
End If
Next ws
End Sub

HTH--Lonnie M.

[Ron de Bruin]

This line you must use in the macro from the other thread

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> Use the left function to remove the last four
>
> ActiveSheet.Name = Left(mybook.Name, Len(mybook.Name) - 4)
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "tcgaines" <[email protected]> wrote in message
> news:[email protected]...
>>
>> After combining worksheets from different workbooks, the name of the
>> worksheets, by default, is the name of the workbook, ie: products.xls.
>> There are many worksheets. I want to implement a loop that will will
>> remove ".xls" from each of the worksheet names. any thoughts?
>>
>> Thanks.
>>
>>
>> --
>> tcgaines
>> ------------------------------------------------------------------------
>> tcgaines's Profile: http://www.excelforum.com/member.php...o&userid=29608
>> View this thread: http://www.excelforum.com/showthread...hreadid=493879
>>
>
>

[tcgaines]

Thank you all for your input. Lonnie, gave your take a run and it worked perfectly.

:]