Remove specific text from all Sheet names

[jdkoeppen]

After testing out some code to compile a directory of workbooks into one master workbook, creating a separate sheet for each original file, the Sheet Names all have a ".xls" in them, which I realize is due to the filename of the original file. However, I don't need this suffix and would like to run a quick script to clean off the ends since I have quite a few sheets now.

Anyone have a nugget o' wisdom? Thankee

[TMS]

Replace All: ".xls" with "". That is, replace all the .xls suffices with nothing

No script needed.

Regards

[jdkoeppen]

Thanks TM but Replace doesn't seem to be looking in the sheet names. I need to know how to edit all the sheets to remove .xls.

[TMS]

Please post a sample of the data in a workbook.

Regards

[jdkoeppen]

Well, I'm not sure how the data in the workbook is relevant as I just need to change the name of the sheet. The sheets all refer to the previous file names of .xls files with names like "CP-1DC.xls" and CP-1EH3.xls". I just want to remove the ".xls" from the worksheet's name tab. There are over 100 tabs and I don't want to edit each one by hand.

[TMS]

The reason for asking for a sample workbook is to make it easier to visualise the problem. If you'd provided a sample workbook with your original post you might have got a definitive answer by return. As it is, it's taken a while longer. ;-)

However, one possible solution:

Please Login or Register  to view this content.

Regards