Excel: Copy and Paste error - VBA one sheet to another

Hi, I need to copy and paste data from column B to sheet "Data" at row A500. Caveat is that rows a1-a400 or so have data in there so I am running into an error. Below is the code I have that is giving me an error. Sub Macro1() Dim lr As Long lr = Range("C" & Rows.Count).End(xlUp).Row Range("B2:B" & lr).Formula = "=A2&':'&C2" Range("E1:E" & lr).Formula = "=IFERROR(VLOOKUP(A1,Data!A:C,2,FALSE), ""NOT FOUND"")" Application.CalculateFull Columns("B:B").Select Application.CutCopyMode = False Selection.Copy Sheets("Data").Select Range("A500").Select ActiveSheet.Paste End Sub

This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of August 03, 2015.

For more resources for Microsoft Excel