Need help for copy pasting data in loop process

[uday]

Hi,

I need a help on below code which will find the match and pasting the data accordingly. Attached is the Excel macro template.

Condition for "if statement"

if the customer id found in "Detail Sheet" then copy and paste the information finding the empty row below the data set.
if not, then do nothing.

I am struggling to write down the loop correctly, when it looking for next customer id in details based on sheet 1 values, its unable to paste the data. Here the help is required.

Sub LST()

'Dim cRng As Range
'Sheets.Add(After:=Sheets("Control Sheet")).Name = "NewSheet"
Dim rCell As Range
Dim rRng As Range
Dim rNum As Long
Dim aRef As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CutCopyMode = False

'Set cRng = Sheets("Detail Sheet").Range("J2:J65000")
Set rRng = Sheets("Detail Sheet").Range("J2:J65000")
Set rCell = Sheets("Sheet1").Range("B2:B65000")
Sheets("Sheet1").Select
For Each rCell In Range("B:B")
 
 srch = rCell.Value
 
 
Set rCell = rRng.Find(What:=srch, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

If rCell Is Nothing Then
    '
Else
    'COPY CUSTOMER ID
'    Sheets("Sheet1").Select
 aRef = rCell.Rows.Value
Sheets("Detail Sheet").Select
    Range("A65000").End(xlUp).Offset(1, 9).Value = aRef
'    ActiveCell.PasteSpecial xlPasteValues
    'Customer Name
      
 
    End If

Next rCell

End Sub

Regards,
Uday

 

[Marc L]

Hi, as a reminder the better explanation the more targeted solution.​

​

if the customer id found in "Detail Sheet" then copy and paste the information finding the empty row below the data set.
if not, then do nothing.

What means paste the "information" ? As guessing can't be coding or try on a mind readers forum rather than any Excel one …​

​

Just attaching the before state and the expected result may help too …​

​