Post row from one sheet to another as result of cell value

[hillarymiller]

So I did write a formula on Sheet1 that told me if a number I scanned was on Page 1 list or not. Now I'm trying to figure out how to post the row of that found number on Sheet1. I figured out how to open Visual Basic. Now I need code.

 

[hillarymiller]

I'm confused since both posts referred to do basically the same thing.


On re-reading post 1 I can see that your request could be interpreted differently - we all just chose the wrong interpretation.

I think you could still do it with another formula in A5 of 'Sheet1'

=IFNA(INDEX('Page 1'!A:C,MATCH(A1,'Page 1'!A:A,0),0),"")

However, if you want vba, try this

Sub Get_Row()
  With Sheets("Sheet1")
    If .Range("A2").Value = "On list" Then
      Sheets("Page 1").Columns("A").Find(What:=.Range("A1").Value, LookAt:=xlWhole).EntireRow.Copy Destination:=.Range("A5")
    End If
  End With
End Sub

Yeeesss! This is perfect! Now, how do I make the VBA update when I put a new value in Sheet 1 "A1"? Like, I'm going to scan a new barcode and I want the code to refresh.

 

[Peter_SSs]

Now, how do I make the VBA update when I put a new value in Sheet 1 "A1"?

Can you explain why you want to use vba and not a formula that would automatically update? (Just trying to understand your circumstances)

I'm not familiar with barcode scanning to Excel but see if this Worksheet_Change event code does what you want.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test. by scanning a new barcode.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    Application.EnableEvents = False
    Intersect(ActiveSheet.UsedRange, Rows(5)).ClearContents
    If Range("A2").Value = "On list" Then
      Sheets("Page 1").Columns("A").Find(What:=Range("A1").Value, LookAt:=xlWhole).EntireRow.Copy Destination:=Range("A5")
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[hillarymiller]

Can you explain why you want to use vba and not a formula that would automatically update? (Just trying to understand your circumstances)

I'm not familiar with barcode scanning to Excel but see if this Worksheet_Change event code does what you want.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test. by scanning a new barcode.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    Application.EnableEvents = False
    Intersect(ActiveSheet.UsedRange, Rows(5)).ClearContents
    If Range("A2").Value = "On list" Then
      Sheets("Page 1").Columns("A").Find(What:=Range("A1").Value, LookAt:=xlWhole).EntireRow.Copy Destination:=Range("A5")
    End If
    Application.EnableEvents = True
  End If
End Sub

I have no good reason to not use a formula. If you have a formula you think would work I'd love to try it!

 

[Peter_SSs]

If you have a formula you think would work I'd love to try it!

From your image it appears 'Page 1' only has 3 columns and there are no blank cells among the data. If that is the situation then try this formula in A5 and copy across to B5 and C5

=IFNA(INDEX('Page 1'!A:A,MATCH($A1,'Page 1'!$A:$A,0)),"")

You will need to remove or disable the other vba code or else that will remove the formulas.

If my assumptions about the 'Page 1' data are incorrect then please provide details/examples as the formula can most likely be adapted.