Populate current sheet from different sheet more efficiently

On a separate excel sheet named "Pricebook", I have near 1000 rows with 2 cells in each row I would like to populate the sheet I am working off when the code is typed in. The below code accomplishes this. Rather than programming 1000 variables is there a way to code this more efficiently. Any advice would be greatly appreciated.

Thank You

Private Sub Worksheet_Change(ByVal Target As Range)

Dim a As Range
If Not Intersect(Target, Range("C21:C36")) Is Nothing Then
For Each a In Intersect(Target, Range("C21:C36"))
If a.Value = Worksheets("Pricebook").Range("A7") Then
Application.EnableEvents = False
Cells(a.Row, "E").Value = Worksheets("Pricebook").Range("B7")
Cells(a.Row, "AA").Value = Worksheets("Pricebook").Range("I7")
Application.EnableEvents = True
End If
Next a
End If

Dim b As Range
If Not Intersect(Target, Range("C21:C36")) Is Nothing Then
For Each b In Intersect(Target, Range("C21:C36"))
If b.Value = Worksheets("Pricebook").Range("A8") Then
Application.EnableEvents = False
Cells(b.Row, "E").Value = Worksheets("Pricebook").Range("B8")
Cells(b.Row, "AA").Value = Worksheets("Pricebook").Range("I8")
Application.EnableEvents = True
End If
Next b
End If

Dim c As Range
If Not Intersect(Target, Range("C21:C36")) Is Nothing Then
For Each c In Intersect(Target, Range("C21:C36"))
If c.Value = Worksheets("Pricebook").Range("A9") Then
Application.EnableEvents = False
Cells(c.Row, "E").Value = Worksheets("Pricebook").Range("B9")
Cells(c.Row, "AA").Value = Worksheets("Pricebook").Range("I9")
Application.EnableEvents = True
End If
Next c
End If

End Sub

Amazing! Thank You! You saved me so much time.

Originally Posted by Bernie Deitrick

Don't use code.

Use this formula in E21:E36
=IFERROR(VLOOKUP(C21,Pricebook!A:B,2,False),"")

And this formula in AA21:AA36
=IFERROR(VLOOKUP(C21,Pricebook!A:I,9,False),"")


If you were to use code, the entirety would be

Please Login or Register  to view this content.