Find Value of First sheet in another and copy related data for that cell value and paste.

Hi All,

I want to Find a cell value of my First sheet (Column A Values) i.e. named as master sheet in another sheet i.e. in statement sheet

If match found then copy related data for that cell value in Master sheet columns.

see attached sheet for more information

i have tried to do it and it is doing for my first criteria (i.e. Opening Balance) but i can't copy other criteria's for the same cell value

Sub test()
Dim FindString As String
Dim Rng As Range
Dim lastrow As Long
lastrow = Sheets("master").Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Sheets("master").Activate
Range("A2").Activate
For i = 1 To lastrow
FindString = ActiveCell.Value
If Trim(FindString) <> "" Then
With Sheets("statement").Range("A:A")
Set Rng = .Find(What:=FindString, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.Offset(1, 1).Copy
Sheets("master").Activate
ActiveCell.Offset(0, 2).PasteSpecial
ActiveCell.Offset(1, -2).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
End With
End If
Next i
End Sub


or sugessest me if there is any another way to do it....!, your help i much appreciated...! waiting for reply...!!!

Deepak,
Please use code tags with your code as per forum's rule.
The code works by matching the headings in master sheet ,row 1, with the headings in the statement sheet.
I have to change some of the names of the headings in master sheet as so to match them with statement sheet.

Hi Thanks for reply....., but i am a beginner for Vba- Macro so, i am not able to understand the code provided by you
and if i want to add My Criteria Tab adjucent to Opening Balance and others then what can i do....

Does the code work for you and if not what is the issue?
Attach a desired result.

Hi AB33,

Yes your code is working perfiectly, but i am just wondoring to add some criteria later on my Master sheet which will be on Statement sheet.

I have updated the New criteria's on example sheet. and please suggest me where should i add new code line or where should i change it (If necessary).

Thanks..!

This should work with any column size so long as the headings match.

It's Working perfectly, Thanks AB33...!