Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
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
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.
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Tutorial: How to pass variables and values to macros. This allows you to get a result from one macr...
Tutorial: In Excel, you can quickly copy an entire worksheet from one workbook to another workbook.Â...
Tutorial: Text files in CSV format are one of the easiest ways to store and transfer data as it is o...
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Tutorial: How to switch a data set in Excel so that the columns become rows and the rows become col...
Tutorial: Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple ...
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- 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.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 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.
- You are now ready to run the macro.