Sub Worksheet_Change Question

[PaulF]

My goal is to make a change on my "IMPORT" sheet that selects the "Release Line by Line" sheet and generates a unique list on the "Unique Lists" sheet.

It works, but selects the range from the "IMPORT" sheet... not the "Release Line by Line" sheet... What am I doing wrong ??

Sub Worksheet_Change(ByVal Target As Range)

Dim E As Object, F As Variant, j As Long, lr1 As String
Set E = CreateObject("Scripting.Dictionary")
Sheets("Release Line by Line").Select

Application.EnableEvents = False

' Skill Craft UNIQUE LIST
lr1 = "P" & Range("P10").End(xlDown).Row + 1
F = Range("P10:" & lr1)

For j = 1 To UBound(F, 1)
  E(F(j, 1)) = 1
Next j
Sheets("Unique Lists").Range("C5:C500").ClearContents
Sheets("Unique Lists").Range("C5").Resize(E.Count) = Application.Transpose(E.Keys)

Sheets("Import").Select

Application.EnableEvents = True

End Sub

 

[NARAYANK991]

Hi Paul ,

See if this works :

Sub Worksheet_Change(ByVal Target As Range)
    Dim E As Object, F As Variant, j As Long, lr1 As String
    Set E = CreateObject("Scripting.Dictionary")

    Application.EnableEvents = False

'   Skill Craft UNIQUE LIST
     With Sheets("Release Line by Line")
        lr1 = "P" & .Range("P10").End(xlDown).Row + 1
        F = .Range("P10:" & lr1)
    End With

    For j = 1 To UBound(F, 1)
        E(F(j, 1)) = 1
    Next j
    Sheets("Unique Lists").Range("C5:C500").ClearContents
    Sheets("Unique Lists").Range("C5").Resize(E.Count) = Application.Transpose(E.Keys)

    Application.EnableEvents = True
End Sub

Narayan

 

[PaulF]

Narayan - BINGO !! We have a winner...

./bow "Thank you sir..."