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
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