none
Check if a work sheet exists, if it doesn't, create a new one with name = Cell.Value RRS feed

  • Question

  • Hi there, I am a rookie at VBA. I am trying to create a macrothat will work down a list in Excel, creating new sheets each time a new data entry occurs with that Cell contents as thename. This is the code I have at the moment, but obviously it doesn't work. Wouldbe great to get some advice on this. (For clarification of the task, I am trying to go down a list ofnumbers, and createa new sheet with that number as the title for everything in the list. However if the number has already got a corresponding sheet, say if the number 120 occurs more than once, I don't want it to try and make a new sheet.)
    Sub CreateSheetsFromAList()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Dim MyCell As Range, MyRange As Range, wsSheet As Worksheet
         
    Set MyRange = Sheets("Sheet1").Range("A3")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    
    For Each MyCell In MyRange
        Set wsSheet = Nothing
        On Error Resume Next
        Set wsSheet = MyCell.Value
        
        If wsSheet Is Nothing Then
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        End If
        
    Next MyCell
    End Sub
    

    Friday, August 14, 2015 11:32 AM

All replies

  • You were almost there. Instead of

            Set wsSheet = MyCell.Value

    use

            Set wsSheet = Sheets(MyCell.Value)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 14, 2015 12:19 PM
  • I managed to fix it before I read this, here's what I got:

    Sub CreateSheetsFromAList()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Dim MyCell As Range, MyRange As Range, ws As Worksheet
         
    Set MyRange = Sheets("Input").Range("A3")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    
    For Each MyCell In MyRange
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
            On Error Resume Next
    Next MyCell
    
    For Each ws In ActiveWorkbook.Sheets
        If Left(ws.Name, 5) = "Sheet" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws
    
    
    End Sub
    

    Friday, August 14, 2015 12:40 PM