Cell Reference on other sheet. Move All Found up to top in order

[taylorbrewed]

Hi, I'm new to excel and cant figure out if I need a macro or not.I have figured out how to reference text in a cell on another sheet in a column. I'm looking for a way to move the information from one column on a sheet to a column in a different tab and to have it constantly stay up top on the sheet in order of it being found on the other sheet. I hope this makes sense. Any help is appreciated. Thanks

Attached is my excel sheet. 19576


Information being referenced is "Sheet" Column A
I need this transferred over to tab "Source"
If text in column A= "Recipe" I need to Text from column B (Same Row) to be referenced on tab "Source" in Column A
If text in column A= "Source" I need to Text from column B (Same Row) to be referenced on tab "Source" in Column B
I need it to ignore all other text that might end up in this column in difference cells.
I want to have it ignore blanks and be moved to the top of tab "Source"

[mdmackillop]

Best to avoid merged cells if possible; also "Sheet" is not a good name for a worksheet; it gets confusing in complex code
Not 100% this is what you are after. It is best to show on your posted sample the expected outcome.

Post this code in the Sheet module. It is Event code and will transfer data when values are entered in Sheet column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row > 4 And Target.Cells.Count = 1 Then
Call SourceData(Target)
End If
End Sub


Private Sub SourceData(Data As Range)
Dim sh As Worksheet
Set sh = Sheets("Source")
With sh
Select Case LCase(Data.Offset(, -1))
Case "source"
'If text in column A= "Source"
'I need to Text from column B (Same Row) to be
'referenced on tab "Source" in Column B
.Rows(2).Insert
.Range("A2:E2").Merge
.Range("F2:J2").Merge
.Cells(2, 6).MergeArea.Formula = "=sheet!" & Data.Address(0, 0)
Case "recipe"
'If text in column A= "Recipe"
'I need to Text from column B (Same Row) to be
'referenced on tab "Source" in Column A
.Rows(2).Insert
.Range("A2:E2").Merge
.Range("F2:J2").Merge
.Cells(2, 1).MergeArea.Formula = "=sheet!" & Data.Address(0, 0)
Case Else
'do nothing
End Select
End With
End Sub