How to remove "-" from a worksheet?

[alexxgalaxy]

I have the following code that could help to remove "-" in the name of all the worksheets in a workbook, except the worksheet "0-Summary". It works well in one workbook but doesn't in the other. The file extension of one of the workbooks is "xls" while the other (the one that the code doesn't work on) is "xlsm". To make the code work on the xlsm workbook, does the code needs changes? If it does, can anyone help with it? Thanks in advance!

Please Login or Register  to view this content.

[kbkumar]

Hi - I tried your code in an .xlsm file and it worked fine for me. What error are you getting while running this in your .xlsm file, can you upload the file to have a look.

Thanks.

[AB33]

If you are running the code from your active workbook, it does not matter what the file extension should be, but if you are accessing through open file path, we need to see what code you have.

[Winon]

Hello alexxgalaxy,

I also find it to work as, kbkumar says. Maybe you could check if the correct Macro is being used in the .xlsm WorkBook. The Code I have used is:

Please Login or Register  to view this content.

Hope that helps!

[Winon]

If you have saved the original .xls File on which the code was working as an .xlsm File, and the Macro was assinged to a Button of some sorts, check and re-assign the Macro to run with "ThisWorkBook".

[alexxgalaxy]

Hi Kbkumar

I didn't run into error. Just the "-" in the name of xlsm worksheets didn't remove like those in the xls worksheets. Basically, it didn't seem to have any impact. I'll try it again and see if it work when I'm back to office next Monday. My excel at home is 2003. It cannot save as a xlsm file.

Thanks for the reply!

[alexxgalaxy]

If you have saved the original .xls File on which the code was working as an .xlsm File, and the Macro was assinged to a Button of some sorts, check and re-assign the Macro to run with "ThisWorkBook".

Hi Winon

I didn't assign the Macro to a Button. I'll try again when I'm back to office next Monday. My excel at home is 2003 and I'm not sure if work the same as the excel 2010 in my office. Thanks so much for the revised code!

[alexxgalaxy]

Hi AB33

I copied the code from the xls workbook and pasted it to the xlsm workbook. I believe I was running the code from my active workbook.

Both Kbkumar and Winon tried the code with a xlsm workbook and it worked well. I don't know why it didn't work on mine. I'll try again and when I'm back to work next Friday. I'm using excel 2003 at home and couldn't open a xlsm file.

Thanks so much for the reply!