Sheet4 drop down in cell J8, will have these selections (example)

Alpha
Bravo
Charlie
Delta

Sheet5 column A will a have the same list in A2 and down.
Column B will have comma separated values like these examples.

one, two, three, four
bbb, xxx,xx
ccc, xxx, zz
ddd, xxx, qq

So "Alpha" in A2 would have "one, two, three, four" in B2,
"Bravo" would have "bbb, xxx,xx" in B3, etc. for the others.

I want a worksheet_change event macro in sheet4 to find the sheet4 J8 value over on sheet5 in column A and then split and transpose the associated value in column B to a list starting in C2.

When a new item is selected on sheet4 drop down in J8, the old list that was in sheet5 C2 is deleted and the new-found split and transformed list replaces it, again starting in C2.

I have this macro so far (not an event macro at present) that fails to make the split and transpose. If I can get the split and transpose line to work, I can write the delete old list and make the code into a event macro myself.

Can't seem to get that line correctly written.

Thanks for taking a look.

Howard

Sub Split_Transpose()
Dim oneRng As Range, ddFound As Range
Dim dDwn As String
Dim X As Variant


dDwn = Sheets("Sheet4").Range("J8")
Set oneRng = Sheets("Sheet5").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)


With Sheets("Sheet5")
Set ddFound = Sheets("Sheet5").UsedRange.Find(What:=dDwn, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Offset(, 1)
If Not ddFound Is Nothing Then


' X = Split(Range("A" & ddFound.Row).Offset(, 1).Value, ",")
Range("C2").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)

Else
MsgBox "No match found."
End If

End With


End Sub