Change Worksheet Names

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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.
1599922757895.png
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

So your tab sheet name are not in right serial?
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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.

VBA 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

Got the code here, Renumbering sheets
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,991
Messages
5,599,242
Members
414,298
Latest member
bongielondy

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
Top