Macro - Create New Worksheets and Name from List

cena1987

New Member
Joined
Jun 12, 2015
Messages
15
Hi all,

I was hoping for some help on some code I have used in Excel. I am a complete novice to all of this and have just been using solutions found on the website.

The following code copies and creates a new worksheet and renames the new worksheet based on a list I have in another worksheet within the same workbook. My problem is this list will be changing over time and when I want to refresh the workbook, I would like the previous worksheets to be replaced with the new list - if that makes sense! Currently when I hit the macro button, it just adds a new set of worksheets after the previously created ones. Here is the code:

Sub AddSheet()
Application.ScreenUpdating = False
Dim bottomA As Integer
bottomA = Range("A" & Rows.Count).End(xlUp).Row
Dim c As Range
Dim ws As Worksheet
For Each c In Range("A4:A" & bottomA)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then
Sheets("ULE").Select
Sheets("ULE").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
End If
Next c
Application.ScreenUpdating = True
End Sub

Any help you can give would be great!

Tom
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Welcome to the forum Tom,

Are you using a specific naming system, the following example code will list the current worksheet names then you would need to have the new names in the next column.

But it maybe useful to know what style of names you are using, Like Week 1, Week 2 etc.

Sub SheetNames1A()
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub
 
Upvote 0
Welcome to the forum Tom,

Are you using a specific naming system, the following example code will list the current worksheet names then you would need to have the new names in the next column.

But it maybe useful to know what style of names you are using, Like Week 1, Week 2 etc.

Hi Trevor. Thanks for replying and the welcome. So the list sits within a Summary sheet which will be changed manually from time to time. The list is three or four lettered codes such as ABC or DXYT. Currently the code I have used copies a sheet named 'ULE' and names it with the first lettered code in my list from a 'Summary' sheet. It repeats this until it reaches the end of the list. I'm sure you understand all this already but I thought I would clarify if it helps! My problem is when I alter that list and run the macro again, it just adds a new lot of worksheets to the end of my workbook rather than updating a replacing the worksheets from the previous run.

Thanks

Tom
 
Upvote 0
Hi Tom try this:

Sub RenameSheets1A()
'Change the 6 to the amount of sheets to change
For i = 1 To 6
On Error Resume Next
'Column B holds the current worksheet names
oldname = Cells(i, 2).Value
'Column A holds the new names
newname = Cells(i, 1).Value
Sheets(oldname).Name = newname
Next

End Sub
 
Upvote 0
Hi Tom try this:

Hi Trevor,

What do I do if I am just changing the existing list, i.e. deleting and adding new codes to the list. Rather than having an old list and new list, I need to just have one list that keeps changing over time. I then need the macro to replace the old worksheets with a new set of worksheets, renamed using the changed list.

Thanks

Tom
 
Upvote 0
That is what the code is doing. You must have a list somewhere otherwise how are you going to recognise which sheets to change?
 
Upvote 0
Hi Trevor,

Ok thanks. May be a better way would be if I can delete all worksheets except for the first two and then run the code I previously had in the first post. Sorry, I am a complete dummy with all of this!
 
Upvote 0
Then this code will delete all the sheets less the ones you want to keep. Good luck!

Sub DeleteMostSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub
 
Upvote 0
tagging on here as this seems really close to what i need.....

Excel worksheet names have a 31 count character limit. I have a macro which in part creates and renames hundreds of worksheets based on a master data sheet in which column E is the source for the names. Some of the names in the master column E are greater than 31 characters. How do i just grab the 31 characters (starting from the left)?
I see many seemingly relevant posts but i just cant seem to grasp it.

Thanks
 
Upvote 0
One suggestion is to use an additional column (helper) and use =Left(Cell,31) this will reduce the content down to 31 characters then use this column to use for the sheet names.

Excel Workbook
EF
1NamesLeft
2Something lIke this does not work with more than 31 charactersSomething lIke this does not wo
3Did you know how to reduce the amount of charactersDid you know how to reduce the
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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