Thread: Vlookup Macro for copy paste of rows

I require a macro, which will copy rows (highlighted in green) in worksheet named LIVE VWAP (Rows 35 and 36), and paste into worksheet named Master Data. This should be done by looking up the date in worksheet LIVE VWAP (Cell A35) and pasting it into the rows of the same date (highlighted in red in row 42 in this example). Worksheet Master Data(2) shows the end results (highlighted in yellow in rows 42&43)

I have tried using Vlookups but cant seem to make it work.

sorry it is not clear what are going to "look up". you want the rows in rows 35 and 36 in thes sheet "live VWAP" is to be copied in the sheet Master data in THE FIRSTS AVAILABLE ROW.

If this is what you want

Code:

Sub test()
Application.DisplayAlerts = False
With Worksheets("live VWAP")
.Range("A35:A36").EntireRow.Copy

With Worksheets("master data")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial

End With
End With
Application.DisplayAlerts = True
End Sub

But this is only copying two rows from one sheet to another sheet. This is not repeated operation. Then why do you need a macro . why not manually copy?

The look up is required to match the dates.
In worksheet named 'LIVE VWAP' the two rows (36&36) start with the date 10/02/2010.
These two rows now have to be pasted into the row with the exact date i.e 10/02/2010 in worksheet named 'Master Data' (which is in row 42). Thus the results you see are in worksheet named 'Master Data(2)' rows 42 and 43.

I could do this manually but I am trying to fit this in along with other function in the macro. Thanks

The code works fine now. I changed the offset value to zero i.e.

.offset(1,0).PasteSpecial

was changed to

.offset(0,0).PasteSpecial

Thanks