Automatically Copy New Data to Another Worksheet

Add to Favorites

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.

35388ea75c2efc5926ce2c3d0b983a42.png

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.






Similar Content on TeachExcel
Pass Values from One Macro to Another Macro
Tutorial: How to pass variables and values to macros. This allows you to get a result from one macr...
Import a Worksheet from One Workbook to Another in Excel
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook....
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
How to import Text Files (CSV) into Excel
Tutorial: Text files in CSV format are one of the easiest ways to store and transfer data as it is o...
Quickly Switch the Columns and Rows of a Data Set in Excel
Tutorial: How to switch a data set in Excel so that the columns become rows and the rows become col...
Apply Worksheet Changes to All Worksheets in Excel
Tutorial: Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Tutorial Details
Downloadable Files: Excel File
Similar Content
Pass Values from One Macro to Another Macro
Tutorial: How to pass variables and values to macros. This allows you to get a result from one macr...
Import a Worksheet from One Workbook to Another in Excel
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook....
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Excel Forum