This macro allows you to quickly and easily copy all new data from one worksheet to another; it also allows you to delete all data from the 'import' worksheet after it has been copied over.
This will save you a lot of time when consolidating data in Excel.
Below, I'll give you the code and an explanation of how to modify it for your use.
Sub Copy_New_Data()
'''''''' TeachExcel.com ''''''''
'Copy all new rows from one worksheet to another.
Dim importSheet, destinationSheet As Worksheet
Dim importLastRow, importColumnCheck, destinationColumnCheck, _
importStartRow, destinationStartRow, curRow, destinationLastRow As Integer
Dim dataToCheck As Variant
Dim rng, rDel As Range
' ------------------------------------------------------------------- '
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| '
'Â Â Â Â Â Â Change this section to work for your workbook.
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| '
' ------------------------------------------------------------------- '
'Set the worksheets
Set importSheet = Sheets("Import") 'worksheet to copy data from
Set destinationSheet = Sheets("backup") 'worksheet to paste new data
'Import data column to check
importColumnCheck = 1
'Destination data column to check
destinationColumnCheck = 1
'Start row on import sheet
importStartRow = 2
'Start row on destination sheet
destinationStartRow = 2
' ------------------------------------------------------------------- '
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| '
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| '
' ------------------------------------------------------------------- '
'Get last row from import worksheet
importLastRow = importSheet.Cells(Rows.Count, importColumnCheck).End(xlUp).Row
'Loop through range
For curRow = importStartRow To importLastRow
  'Get data to check
  dataToCheck = importSheet.Cells(curRow, importColumnCheck).Value
  'Get last row from destination sheet
  destinationLastRow = destinationSheet.Cells(Rows.Count, importColumnCheck).End(xlUp).Row
  'Check for duplicate
  With destinationSheet.Range(destinationSheet.Cells(destinationStartRow, destinationColumnCheck), destinationSheet.Cells(destinationLastRow, destinationColumnCheck))
    Set rng = .Find(What:=dataToCheck, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
    If Not rng Is Nothing Then
      'Record already exists
      'mark rows for deletion
      If Not rDel Is Nothing Then
        Set rDel = Union(Range("A" & curRow), rDel)
      Else
        Set rDel = Range("A" & curRow)
      End If
    Else
      'New record, so copy it over
      importSheet.Range("A" & curRow).EntireRow.Copy destinationSheet.Range("A" & destinationLastRow + 1)
      'mark rows for deletion
      If Not rDel Is Nothing Then
        Set rDel = Union(Range("A" & curRow), rDel)
      Else
        Set rDel = Range("A" & curRow)
      End If
    End If
  End With
Next curRow
'Delete rows that need to be deleted
'Un-comment the next line of code if you want to delete copied rows.
'rDel.EntireRow.Delete
End Sub
Change this section of the code to the correct names of the sheets from which you will copy your data and to which you will paste your data.
'Set the worksheets
Set importSheet = Sheets("Import") 'worksheet to copy data from
Set destinationSheet = Sheets("backup") 'worksheet to paste new data
Import is the name of the sheet from which you will copy data.
backup is the name of the sheet to which you will paste the data.
Change these as needed.
This is the first row from which you will copy data. Do not include the header row.
'Start row on import sheet
importStartRow = 2
'Start row on destination sheet
destinationStartRow = 2
Right now, the import and destination rows start on row 2.
You need to select one column in which to check for duplicate rows. For instance, you could check an ID or OrderNumber, basically anything that must always be unique for each record or row of data.
'Import data column to check
importColumnCheck = 1
'Destination data column to check
destinationColumnCheck = 1
This checks in column A for duplicate values.
1Â means column A; 2 means column B; 3 means column C; and so on.
If you want all records that have already been copied over or already exist on the destination sheet to be deleted from the Import worksheet, uncomment the following line:
'rDel.EntireRow.Delete
This is at the very end of the macro.
To uncomment, remove the single quotation mark from the start of this line of code.
This is a powerful little macro and it is pretty easy to customize for your needs. I recommend using it on sample data first though to make sure you get the desired results.
Make sure to download the sample file attached to this tutorial to get the macro and some sample data with which to test the macro.