Hello, I would really appreciate some help please.
I am attempting to
- copy an entire row of data from sheet 1 to a new blank row in sheet 2
- when the data in Cell A of the source row in sheet 1 matches a list on sheet 3
This is my code:
Dim srchLen, fName, nxtRow
Dim f As Range
'Clear Sheet 2 and Copy Column Headings
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column A, copy it top the next row in Sheet2
For fName = 2 To srchLen
Set f = .Find(Sheets(3).Range("A" & fName))
If Not f Is Nothing Then
nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
f.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRow)
Sheet 3: Dynamic List (sample below)
Currently when I run the macro it only gives me the first instance of each fruit, instead of giving me all instances of the desired fruit.
Outcome from sample above - Sheet 2 is populated with the following:
Fruit Quantity Price Farmer
Apples 8 $2.00 ABC
Bananas 3 $0.50 ABC
Grapes 12 $4.00 GHI
Raspberries 8 $4.00 MNO
My 'next' does not appear to be working. Desired outcome is that it lists all rows with fruit that matches the list on sheet 3 (I should have 3 rows of Apples, 2 rows of Bananas, 1 row or Raspberries, 1 row of Grapes). I would really appreciate if someone could help me fix this code, I've been struggling with it and research resources online for hours.
Thank you in advance for your time and consideration (need some sleep now, fresh eyes in the morning).