Automatically Copy New Data to Another Worksheet

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.

Sections:

The Macro

Use the Macro

Notes

The Macro

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

Where to install macro: Module

Use the Macro

Choose Worksheet to Copy from and Paste To

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.

Change Start Row for Copying/Pasting

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.

Change Comparison Column - Column that is Used to Check for Duplicate Rows

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.

Delete All Records after Being Copied to the New Sheet

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.

Notes

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.

---