Renumbering sheets - run-time error 1004

nybpete

New Member
Joined
Jun 9, 2017
Messages
5
I have used the code below to change the codename ie sheet1, sheet2 etc to match the order of the sheets in the workbook. Now when I try to run it I get a run-time error 1004. I'm not sure what changed or what could be causing the error. I think I got much of the code from this board or one like it in the past, but didn't note where. Any ideas what could cause the run-time error?

Code:
Sub Renumber_Sheets()


Dim ws As Worksheet
Dim x As Integer


x = 3000 'a number greater than the total number of sheets in workbook


For Each ws In Worksheets  'rename all sheets to avoid conflicts
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = "Sheet" & x
x = x + 1


Next
  
x = 1 'start with first sheet in workbook


For Each ws In Worksheets 'rename all sheets to sheet order
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = "Sheet" & x
x = x + 1
Next
  
  
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I find that it can often be difficult to try to figure out exactly what someone is trying to do from code that does not work properly, especially if it wasn't something they wrote themselves but something else they found and tried to manipulate.

Why not just tell us in plain English exactly what it is you are trying to do? Include naming conventions of your sheets and any other pertinent information.
 
Last edited:
Upvote 0
OK... Let me try again. I want to renumber the excel object reference or codename to match the order of the sheets in the workbook.
For instance if I have 3 sheets bananas, coconuts, and apples created in that order the excel objects show as:
Sheet1(bananas)
Sheet2(coconuts)
Sheet3(apples)

If I move the sheets in the workbook to alphabetical order the Sheet# references in the excel objects don't follow and I still have:
Sheet1(bananas)
Sheet2(coconuts)
Sheet3(apples)

The code I originally posted used to rename the sheets so that once the sheets are placed alphabetical order (apples, bananas, coconuts) the excel objects show up as:
Sheet1(apples)
Sheet2(bananas)
Sheet3(coconuts)

The error happens on this line:
Code:
ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = "NewName"

The rest of the code is just 2 simple for loops I created. The first loop is intended to rename all the sheets to a number that wouldn't be used, this is to avoid conflicted codenames. The second loop then renames all the sheets in the worksheet order of the workbook starting at 1.
 
Upvote 0
Well, I don't get any error when I run it, but it does not seem to do anything for me.
Can I ask why it is important to change this?

If I know the big picture of that you are trying to accomplish, I may be able to offer other solutions.
 
Last edited:
Upvote 0
It's really not all that important now other than to help my OCD at this point.

I have been using it on a workbook with over 100 sheets. When I originally made the workbook they weren't alphabetized, but I used some other code to alphabetize the sheets and create an alphabetized list of the sheets. When the sheets were alphabetized the sheet# jumped all over the place. I had some ideas for using the line number of the item in the list and referring to the corresponding sheet number. (I found better ways to do what I needed to do). I recently added a few more sheets and alphabetized, and of course they were no longer in order so I tried running this code that worked previously and it no long seems to work for me. It bugged me that it wasn't working and my sheets were no longer in order. I was hoping it might be a simple setting or add-in that changed inadvertently.
 
Upvote 0
It would be interesting to know which version of Excel you are using. I am using Excel 2010, and it appears that the code doesn't return any errors, but also doesn't seem to do anything for me.
 
Upvote 0
It originated in Excel 2010. I recently got a new computer with 2013 on it and have been using that. I have tried it on both computers and get the error. I even tried an old version of the spreadsheet that had never been in 2013 on the 2010 computer and still get the error.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top