VBA create new excel if cell value changed and it's unique value in range

Question

I have a master sheet where I enter name in column "B" from cell "B4:B50000" and in column "E" from cell "E4:50000" now when cell value change in this range than first check whether it's unique value or not, if it's unique value than create new sheet in same workbook with that unique name

I also want to have drop down list where all unique value from range B4:B50000 and E4:E50000 auto suggest matching name

e.g. In cell B4 name written is salman now i am typing name in cell B5 and after writing sal,it should be suggesting unique names starting from sal

I have tried following code from my side but as I am beginner in VBA I got partial success only, kindly help to fix it up

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, sh As Worksheet
With ActiveSheet
For Each c In .Range("B3:B50000", .Cells(Rows.Count, 1).End(xlUp))
    Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
    sh.Name = c.Value
    Set sh = Nothing
  Next
 End With


End Sub

Any help ll be appriciated, thx in advance.

See [this post](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) to properly identify the last row — urdearboy, Dec 11 '20 at 17:02
You have many questions nested here. This site is for specific questions. Please modify your question to focus on one issue. Do you need help identifying a unique range? creating a drop down? etc. etc. One problem at a time. You shared code but have not told us what is wrong with it. Does it give error? Wrong result? No result? etc — urdearboy, Dec 11 '20 at 17:04
For suggesting values in drop down - have you tried a google search? I found many solutions that you can try to recreate. [here](https://www.extendoffice.com/documents/excel/2401-excel-drop-down-list-autocomplete.html#a1) and [here](https://stackoverflow.com/questions/19818075/excel-data-validation-with-suggestions-autocomplete) for example — urdearboy, Dec 11 '20 at 17:08
@urdearboy thx for drop down help, it works, pls also help in main question — Jigar Donda, Dec 11 '20 at 17:12

score 0 · Accepted Answer · answered Dec 11 '20 at 17:25

Add a new worksheet for Unique values entered into Column B

Make sure the change event is only activated when change occurs in your target column (B) using Interest
Loop for each new value added to target column vs every item (what you are doing)
Use CountIf to isolate unique values
Check to make sure the sheet being added does not already exist (UDF from Tim Williams)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Range, ws As Worksheet

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each x In Target
        
        If Application.WorksheetFunction.CountIf(Range("B:B"), x) = 1 Then
            If Not WorksheetExists(x.Value, ThisWorkbook) Then
                Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
                ws.Name = x.Value
                Set ws = Nothing
            Else
                MsgBox "Worksheet Already Exists With Name '" & x.Value & "'", vbCritical
            End If
        End If
    
    Next x
End If
        
End Sub

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean

    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
        On Error Resume Next
            Set sht = wb.Sheets(shtName)
        On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
    
End Function

VBA create new excel if cell value changed and it's unique value in range

1 Answers1