Excel VBA to copy a row range from one sheet to a particular row in another

Question

I have searched extensively for the thing that I need but only found this previous post but does not entirely help me in what I need (it is the closest one though).

I am creating a tracker to keep track of records on my Excel sheet where I get the raw dump of the data from a source.

"Tracker" Sheet

Column A      Column B      Column C        Column D
Ref#          App name      Date            My own Comments
001           Excel         01/01/2015      tracking deployment on 1/1
002           Word          02/02/2015      tracking decom  on 2/2
003           PPT           03/03/2015      tracking upfrade on 3/3

"Raw Data" Sheet

Column A        Column B    Column C
Ref#            App name    Date
001             Excel       01/02/2015
002             Word        02/03/2015
003             PPT         03/04/2015

I need a macro to copy row range A1 to C1 from Raw Data to Tracker without overwriting my Column D . Also need the macro to paste that only on the row where the reference is already existing so my comments are valid. For example, it will look at ref 001 from Raw Data and look that up in Tracker, if it exists, then copy on that same row. Else, create a new row with that information. The dates changes every now and then based on the raw data and I want to be able to track those changes based on the tracker column d's cells for reporting.

Please let me know if this is workable in Macro as installing a 3rd party software in my PC may not be an option.

Answer 1

Dim dataSheet As Worksheet
Dim copyToSheet As Worksheet

Set dataSheet = Sheets("Raw Data")
Set copyToSheet = Sheets("Tracker")

'Unhide the sheet to dump data on
copyToSheet.Visible = True

dataSheet.Activate

sheet1LastRow = dataSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

dataSheet.Range("A1:C" & sheet1LastRow).Copy
copyToSheet.Range("A1").PasteSpecial xlPasteValues

This should do what you want. You may have to change the sheet names at the top if I have put them in incorrectly :)

Also you can change ".PasteSpecial xlPasteValues" to ".Paste" instead if you want to do the equivalent of hitting Ctrl+V instead of Pasting only Values.