Excel VBA to Match Cell in Summary to Workbook Name and Copy information to Summary

[Iris14]

Hello, I have a REIT summary worksheet which contains different account names starting from Cell A4.

Within the whole workbook, there are also different tabs, each named by the account name and contained information of that account.

I would like to build a macro which find the account name in the REIT summary tab that matches its respective tab and copy cell information from the last cell of column G in that worksheet to the REIT summary tab in column B.

This is what I had so far and it does not work... I guess the reason is because I have put two FOR functions in the macro.

Sub Macro1()


Dim x As Range
Dim wkSht As Worksheet


Set x = Range(Worksheets("REIT Summary").Cells(4, 1), Worksheets("REIT Summary").Cells(Rows.Count, 1).End(xlUp))
For Each Cell In x
For Each wkSht In Sheets

On Error Resume Next


If Sheets("REIT Summary").Cells("x").Value = wkSht.Name Then

wkSht.LastRow = Cells(Rows.Count, 1).End(xlUp).Row
wkSht.Cells(LastRow + 1, 7).Value.Copy
Sheets("REIT Summary").Cells(x, 2).PasteSpecial xlPasteAll

End If


Next
Next


End Sub


[h=1][/h]
I am a totally beginner for writing macro.. any help would be greatly appreciated !!! Thanks !