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

 

[Mr_Ragweed]

Trevor G - thank you for responding. I like the simplicity of that formula. I need to translate it into VBA though. My knee jerk reaction is that should be easyish right?

 

[Trevor G]

Look at this example for using Left within VBA

Sub useLeft1A()
ActiveSheet.Name = Left$(Range("c3"), 6)
End Sub