Copy new cells to another sheet

Microsoft office Excel 2010
March 25, 2010 at 15:56:47
Specs: Windows 7
Hello. I am a beginner in VBScript and i need help with a script to copy new rows from the first sheet(Online) to the second sheet(Database). Only rows that are not in the second table will be inserted in sorted order.
Sorry for my English.

1 a x
2 b y
3 c z

1 a x
3 c z
4 d w

-Run script

1 a x
2 b y
3 c z
4 d w

With Worksheets("Online")
		'how to select only new rows'
    Set rndSource = ???
    For Each rndRow In rndSource
		'or if new for Database'
        If rndRow.Text = ??? Then
		'how to insert (not Copy) in sorted order in Worksheets("Database")'
            rndRow.EntireRow.Insert Destination:= ???
        End If
    Next rndRow
End With

Thanks in advance.

See More: Copy new cells to another sheet

Report •

March 25, 2010 at 22:31:47
First, I should mention that we're working with VBA not VBScript. VBA is Visual Basic for Applications and the syntax is very specific to the application (i.e. Excel) that the code is written for.

In addition, when VBA is used within the MS Office suite of applications, the code is usually referred to as a macro, not a script.

Finally, I don't have Excel 2010. This code was written in Excel 2003 and I am assuming it will work in Excel 2010.

Any code provided in this (or any) forum should be used in a back-up copy of your workbook. Since macros can not be undone, it is highly recommended that you test it on a back up copy of your data.


The code below is based on the example data you provided.

It assumes that both Column A of the Online sheet and Column A of the Database sheet contain only one instance of each piece of data. The code uses Find to see if each piece of data in Column A of the Online sheet exists in Column A of the Database sheet. If it doesn't find it, it then copies the entire row. Once all of the "new" rows have been copied, it sorts the Database sheet based on Column A.

If your actual data has "duplicates" in Column A, then you'll need to tell us that, and perhaps tell us if there is any column that contains unique values.

Let us know how this works out for you.

Option Explicit
Sub CopyNewRows()
Dim last_olRow, last_dbRow As Integer
Dim c, olData
'Find last row in Column A of Online and Database sheets
 last_olRow = Sheets("Online").Range("A" & Rows.Count).End(xlUp).Row
 last_dbRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Online Column A looking for
'each piece of data in Database Column A
   With Sheets("Database").Range("A:A")
    For Each olData In Sheets("Online").Range("A1:A" & last_olRow)
     Set c = .Find(olData, lookat:=xlWhole)
'If data from Online isn't found in Database then
'increment last row counter from Database sheet and
'Copy the entire row to the next row in Database
      If c Is Nothing Then
        last_dbRow = last_dbRow + 1
        olData.EntireRow.Copy _
          Destination:=Sheets("Database").Range("A" & last_dbRow)
      End If
   End With
'Sort the Database sheet
  Sheets("Database").Cells.Sort _
   Key1:=Sheets("Database").Range("A1"), Order1:=xlAscending, Header:=xlGuess
End Sub

Report •
Related Solutions

Ask Question