I have a macro where when I run the macro, Excel will automatically look at the contents in A2 and below, and will create a sheet with a matching name as the cell contents. If I have data in cells A2:A5 for example, and I run the macro, a new sheet will be created for each item in A2:A5 and the name will match that cell's contents.
What I am wanting however is that if I add an item to A6 for example, and run that same macro, that the macro will Ignore and not create a sheet for the values from A2:A5 (since they already exist) and will just create the 1 new sheet matching the item in cell A6.
My code that I'm currently using is below.
- Sub AddSheetsreset()
- Dim xRg As Excel.Range
- Dim wSh As Excel.Worksheet
- Dim wBk As Excel.Workbook
- Set wSh = ActiveSheet
- Set wBk = ActiveWorkbook
- Application.ScreenUpdating = False
- For Each xRg In Selection
- ' Fill in the cell range above of the stories '
- With wBk
- .Sheets.Add after:=.Sheets(.Sheets.Count)
- On Error Resume Next
- ActiveSheet.Name = xRg.Value
- If Err.Number = 1004 Then
- Debug.Print xRg.Value & " already used as a sheet name"
- End If
- On Error GoTo 0
- End With
- Next xRg
- Application.ScreenUpdating = True
- End Sub