Macro - Create New Worksheets and Name from List

[cena1987]

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

 

[Trevor G]

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

 

[cena1987]

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

 

[Trevor G]

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

 

[cena1987]

ADVERTISEMENT

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

 

[Trevor G]

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

 

[cena1987]

ADVERTISEMENT

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!

 

[Trevor G]

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

 

[Mr_Ragweed]

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

 

[Trevor G]

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
	E	F
1	Names	Left
2	Something lIke this does not work with more than 31 characters	Something lIke this does not wo
3	Did you know how to reduce the amount of characters	Did you know how to reduce the
Sheet1