Change Worksheet Index

**dmeinder** · 10-03-2012, 12:52 PM

I need help changing the index of a worksheet. the reason is that I'm working on a standardized spreadsheet for a client that has standardized code already in it.....but the existing code makes reference to worksheets by means of "Sheet1", "Sheet2" etc. Essentially the sheet index. Now I could do a find replace, but then the next project I'd have to do the same thing.

So I need a means in VBA to change the sheet index number to what the file expects after my manipulations.

Essentially, what is the code to change, for example, Sheet2 to Sheet1?

Thanks in advance

**patel45** · 10-03-2012, 01:24 PM

Sheets("Sheet2").Move before:=Sheets("Sheet1")

**dmeinder** · 10-03-2012, 01:44 PM

Thank you for the response.

The problem with that is it assumes Sheet1 exists. For example, lets say you have 4 sheets, Indexed 1 to 4. If you delete sheet index 2 and 3 then you'd be left with sheet index 1 and 4......now if using the above method to change sheet4 to index 2 you'd get an error as sheet3 doesn't exist.

I know you can then use "after" with the above syntax, but one cannot always account for that, therefor a way to explicitly designate a sheet index would be preferable.

Thoughts anyone?

**HaHoBe** · 10-03-2012, 01:44 PM

Hi, dmeinder,

could you please bring up an example of

... by means of "Sheet1"

If it´s something like

Please Login or Register  to view this content.

no changes are need to be made as this indicates the codename of the sheet you can find in the VBE.

If it´s

Please Login or Register  to view this content.

I would use constants instead of the sheetname and change it/them at the top of the procedure.

I doubt it´s something like

Please Login or Register  to view this content.

Ciao,
Holger

**dmeinder** · 10-03-2012, 02:03 PM

A little more debugging has con-screwed my thoughts of sheet index, and after some more research I need to change the sheet CodeName property.

When I refer to screen index I mean the placement in "Microsoft Excel Objects" in the VBA editor and not the order as viewed on the tabs or the excel workbook.

Example
Sheet1(ALARMS)
Sheet3(CONTROL)
Sheet4(STATUS)

Where i want to move Sheet4 to sheet2 in this case to have:
Sheet1(ALARMS)
Sheet2(STATUS)
Sheet3(CONTROL)

This way all existing code in my clients workbook that references Sheet2 in VBA will still work. (note that existing VBA does not reference "STATUS" in this case)

I've since found out that the sheet CodeName property is read only, but I really need a workaround for this?

Thanks again for the help

**dmeinder** · 10-03-2012, 04:58 PM

Well I figured it out....and thought I'd post the results.

Please Login or Register  to view this content.

Where "SheetCodeName" or type string is the desired CodeName Property and "OrigSheetName" of type string is the desired sheet name in which to change CodeName.

Note that this codes tries to break with continue option while in debug mode but it works just fine when ran as macro.

Cheers, I'll post the complete reason for this code, as the intent is deeper and is to rectify the macro undo problem with code.