Index match copy paste

[samitnair]

Hi

I require assistance in filling cells in a pre formated letter OR match 2 criteria and paste its next 4 column values.

Destination Sheet: "Letter"
Data Sheet "AIO"

If i enter customer name (B6) and address (B7) in sheet "Letter" the macro must match the same in columns A & B respectively in sheet "AIO".If match found copy data from columns C,D,E,F and paste it in sheet "Letter" (D18). I have inserted a screenshot of sheet "AIO" for better understanding.....

I have made this question a bit twisted but simply IF the values in "red" matches I want the "green" values to be copied pasted in sheet "letter".

Thanks

 

[Andrew Poulsom]

Use AutoFilter and copy the visible rows.

 

[samitnair]

Hi

Thanks for the suggestion....but you are only watching a part of the sheet....and copy pasting is a very time consuming.....Please suggest a automation method

If i enter customer name (B6) and address (B7) in sheet "Letter" the macro must match the same in columns A & B respectively in sheet "AIO".If match found copy data from columns C,D,E,F and paste it in sheet "Letter" (D18).
(Coloring is done for your understanding)
thanks

 

[samitnair]

SOS..Please am going crazy figuring how to copy and paste using vb

 

[Andrew Poulsom]

Didn't you get a solution here?

http://www.mrexcel.com/forum/showthread.php?t=535254&page=2

 

[samitnair]

I have tried all ways to modify the same code but i am only getting errors and its not working..... Ok i will again try to modify the same and hope if i get a breakthrough

 

[samitnair]

Hi.

PLEASE IGNORE THE PREVIOUS POST

Am using 2 sheets
1. Sheet "AIO" (sheet with data)
2. Sheet "Letter" (Result Sheet)

I need a code to copy C:H from Sheet "AIO" if A4 and A5 in sheet "Letter" matches anywhere in column A and B in sheet "AIO". The copied data is to be pasted in sheet "Letter" B15. I tried many posts and codes but coz am new to dis am going in circles.

I tried modifying the code in the above link and I have pasted it below...Please correct where am going wrong or any workaround for this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CustName As String
Dim CustFound As Range
Dim FirstRow As Long, LastRow As Long, r As Long, rws As Long

Const NameCell As String = "A4"

If Not Intersect(Target, Range(NameCell)) Is Nothing Then
CustName = Range(NameCell).Value

Application.EnableEvents = False

If Not IsEmpty(Range("AIO")) Then
Range("15").CurrentRegion.EntireRow.Delete
End If

If CustName <> vbNullString Then
With Sheets("AIO")
Set CustFound = .Columns("A").Find(What:=CustName, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If CustFound Is Nothing Then
MsgBox CustName & " not found"
Else
FirstRow = CustFound.Row
r = FirstRow
LastRow = .Range("G" & .Rows.Count).End(xlUp).Row
Range("B15").Resize(rws, 3).Value = _
CustFound.Offset(, 3).Resize(rws, 3).Value
End If
End With
End If
Application.EnableEvents = True
End If
End Sub

Thanks