Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Worksheets
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" wrote in message ... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Worksheets
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Worksheets
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Worksheets
This line you must use in the macro from the other thread
-- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Worksheets
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming Worksheets
Thank you all for your input. Lonnie, gave your take a run and it worked perfectly. :] -- tcgaines ------------------------------------------------------------------------ tcgaines's Profile: http://www.excelforum.com/member.php...o&userid=29608 View this thread: http://www.excelforum.com/showthread...hreadid=493879 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming worksheets | Excel Worksheet Functions | |||
RENAMING WORKSHEETS | Excel Worksheet Functions | |||
Renaming Worksheets | Excel Worksheet Functions | |||
Renaming Worksheets | Excel Programming | |||
Renaming worksheets! | Excel Programming |