Change Worksheet Names

[pbt]

Is there a way to Renumber Worksheet names, that is Sheet 1, Sheet 2, etc, in the Properties Window on the fly with VBA?

I have a Master workbook with code that will take all worksheets from three different workbooks with multiple worksheets, and copy all worksheets from the three into the Master. This works fine

The problem that I am experiencing is all the sheets in the three workbooks have a sheet Name as a two digit number. After the Macro is ran, I get all my sheets from the three into my master, but I also get three additional sheets at the end. Sheet1, Sheet1 (2), and Sheet1 (3). All of which doesn't have any information on it. These three addition sheets are not needed and I don't what to have to do the easy step of just deleting them. I rather just not have them appear.

My thought is I have to Rename the worksheets in the three Workbooks starting with the first worksheet in Each workbook starting with Sheet1, so it doesn't happen,

The task would be a time consuming thing to do for every Workbook. That is why I'm reaching out for a better solution. BTY there are about 10-15 worksheets in each workbook.

Thanks
pbt

 

[mehidy1437]

This might be helpful for you.
Sheet name back to internal name

 

[pbt]

This might be helpful for you.
Sheet name back to internal name

Thanks for the reply. Late for me tonight, but will look into it tomorrow.

 

[pbt]

I took a look at the above link, but it is not what I need to do. My sheet tab names are okay. I am trying to change the Code Name of each sheet in the Properties window without typing it in for each sheet. Right now the code name is anywhere for Sheet20 to Sheet90 and is not consecutive because of additions and deletions thought the years. Actually there are only about 15-20 sheets in each Workbook.

I would like to change the Code Names to start at Sheet1 until the last worksheet in the Book.

Using Excel 2019.

 

[mehidy1437]

As you mentioned your sheet tab name is okay, if you turn your tab name into code name, will it be okay? Let me know.

 

[pbt]

I don't want to change the worksheet Tab name at all. I want to renumber the the sheets in the Properties window of VBA project window.

 

[mehidy1437]

So your tab sheet name are not in right serial?

 

[pbt]

mehidy1437

Thanks for your interest and hanging in there. Tab names are not my issue at all. I am happy with what the Tab names are. Refer back to post #6 & post #4. I want to change the Code Names back to Sheet1 through whatever the count of worksheets are in the particular workbook in the Properties Window. As it is now, one of the workbooks starts with Sheet20 to whatever. If I insert a new worksheet it is named Sheet1 by default because Excel recognizes Sheet1 thought Sheet19 are available.

I am looking to rename all worksheet Code Names to start at Sheet1 to whatever Sheet# so when I do insert a new worksheet it takes on the code name at the end, not one that is available. And as I mentioned in Post #4, I don't have that many sheets in each workbook but the Names are all over the board.

Thanks

 

[mehidy1437]

Hi, the below code works in my test, check & see if it works for you or not?

Just be sure to have the programmatic access to the visual basic project.
Excel->File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object model.

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

Got the code here, Renumbering sheets