Renaming worksheets while shortening their names

What I am trying to do is to rename a worksheet by removing some characters from its name and replacing them with others.

For example I have the following two worksheets

xxxxx_data
yyyy_data

I am creating embedded charts from data from these two worksheets and I want to rename the charts appropriately as:

xxxxx_chart
yyyy_chart.

The only thing I have managed so far is to ADD a piece of text at the end of the existing worksheet name, while what I want is to remove a specified number of characters and then add my bit.

Any ideas?

Hi,

Please Login or Register  to view this content.

HTH

Thank you Richard but this is not what I am looking for.

I am not looking for the code to simply rename a known worksheet.

I have numerous worksheets with random names but always with the suffix "_data". What I am looking for is a way to copy them and replace the "_data" suffix with something else that I specify. Or an alternative would be to copy them while stripping the last X number of characters from the worksheet's name.


Regards,
K

Hello kostas,

I'm a little confused by what you have posted. Your first post mentions renaming embedded charts. Now, you want to rename worksheets.Can you give a short and clear example of what you want to do?

Sincerely,
Leith Ross

Hi Leith,

I am sorry if I have not clarified what I am trying to do.

Here is how my macro works so far.

Steps:
1) Open a "Open file window" where I can select some excel files
2) Open the selected spreadsheets
3) Copy the worksheets from these spreadsheets to my Master workbook (the one that has the macro)
4) Perform some calculations on the data contained in the copied worksheets
5) Create some charts (not embedded) based on the data of the copied worksheets

Now, when I copy the worksheets from the spreadsheets to my Master workbook I add a suffix "_data" to their name.

When I create the chart sheets I add an additional suffix to the worksheet's name, "_chart". Therefore I end up with (for example) a list like this:

Sheet1_data
Sheet2_data
Sheet3_data
Sheet1_data_chart
Sheet2_data_chart
Sheet3_data_chart
Macro_worksheet

What I want to achieve is when I create the chart sheets, instead of simply adding a suffix "_chart" to the sheet's name after the "_data" bit, to strip the "_data" part from the sheet name and simply get a Sheet1_chart name.

I guess this has to be done by specifying a number of characters to remove from the source worksheet name (i.e sheet1_data) and then add the new suffix, or alternatively replace a specific number of characters (or string) with another one.

I hope this makes it clearer, please let me know if you need more details.

The thread containing the macro can be found here (see last post for the final version)

Please Login or Register  to view this content.

Regards,

K

Maybe like this:

Please Login or Register  to view this content.

Hello kostas,

Since you can add "_chart" to the sheet name, I am puzzled as to why you just don't rename the sheet? Changing the sheet name is a simple matter. It doesn't even need to be selected to change its name. It is important to note that when working with Chart sheets (not embedded charts) and Worksheets to use the Sheets collection object to refer to either one.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

shg: Thank you very much. Your solution worked just fine! Valuable lesson as well!!!

Leith: I am afraid the code you suggest doesn't work as it should in my macro. First of all I get an error on the first line "sim sht as sheet". I imagined the mistake was sim (dim), corrected it but error again. I changed "sheet" to "sheets" and then it broke below at sht.name.

The sheets I am trying to rename are chart sheets so I don't know if that requires any major change in the code.

I couldn't figure out how to do the rename with the * and I also didn't think of combining the Instr to compare the data strings in such a way, therefore I didn't follow that (absolutely logical!) path! Thank you though for your suggestion as it gave me other ideas and really helped in a better understanding of vba.