Copy rows with a difference
 

--------------------------------------------------------------------------------

Hi All

I have 2 sheets of data

Sheet 1 and sheet 2 ( with columns a:z - the number of rows varies )

I have a Macro that will pull entries from sheet 2 that are not on
sheet 1 to a new sheet "sheet 3". I have put this below, But I can only
make this work to pull out the column A.

Does anyone know how to modify this to pullthe entire row to sheet 3
and not just column A

thanks for any help

David

'
' Macro1 Macro
' Macro recorded 15/11/2004 by DavidBu
'
Sub UniqueList()
'Build a new unique list from two other lists.
'Works by checking list 1 for missing values in list 2.
'Note: List 1 cannot contain duplicates, but list 2 can!
'Standard Module code!
'Sheet1 is the master list of names.
'Sheet2 is the Raw data.
'Sheet3 gets the names that are in the raw data but not on the master
list!

Set MyFunction = Application.WorksheetFunction
Set MyRange1 = Sheets("Sheet1").Range("A2:A5000")
Set MyRange2 = Sheets("Sheet2").Range("A2:A5000")
Set MyResults = Sheets("Sheet3").Range("A2")

'Loop for list values.
Sheets("Sheet1").Select
For Each cell In MyRange2
On Error GoTo myFin
Sheets("Sheet2").Select
If MyFunction.CountIf(MyRange1, cell.Value) = 0 Then
Sheets("Sheet3").Select
Set MyResults = MyResults.Offset(r, 0)
MyResults.Value = cell.Value
r = r + 1
End If
Next cell

myFin:
Sheets("Sheet3").Select
Sheets("Sheet3").Range("A1").Select

End Sub


--
DB100
------------------------------------------------------------------------
DB100's Profile: http://www.excelforum.com/member.php...fo&userid=9176
View this thread: http://www.excelforum.com/showthread...hreadid=320116

Copy rows with a difference
 

change

Set MyResults = MyResults.Offset(r, 0)
MyResults.Value = cell.Value

to

Set MyResults = MyResults.Offset(r, 0)
cell.Resize(1,26).Copy
MyResults.Pastespecial xlValues

--
Regards,
Tom Ogilvy


"DB100" wrote in message
...

--------------------------------------------------------------------------
------

Hi All

I have 2 sheets of data

Sheet 1 and sheet 2 ( with columns a:z - the number of rows varies )

I have a Macro that will pull entries from sheet 2 that are not on
sheet 1 to a new sheet "sheet 3". I have put this below, But I can only
make this work to pull out the column A.

Does anyone know how to modify this to pullthe entire row to sheet 3
and not just column A

thanks for any help

David

'
' Macro1 Macro
' Macro recorded 15/11/2004 by DavidBu
'
Sub UniqueList()
'Build a new unique list from two other lists.
'Works by checking list 1 for missing values in list 2.
'Note: List 1 cannot contain duplicates, but list 2 can!
'Standard Module code!
'Sheet1 is the master list of names.
'Sheet2 is the Raw data.
'Sheet3 gets the names that are in the raw data but not on the master
list!

Set MyFunction = Application.WorksheetFunction
Set MyRange1 = Sheets("Sheet1").Range("A2:A5000")
Set MyRange2 = Sheets("Sheet2").Range("A2:A5000")
Set MyResults = Sheets("Sheet3").Range("A2")

'Loop for list values.
Sheets("Sheet1").Select
For Each cell In MyRange2
On Error GoTo myFin
Sheets("Sheet2").Select
If MyFunction.CountIf(MyRange1, cell.Value) = 0 Then
Sheets("Sheet3").Select
Set MyResults = MyResults.Offset(r, 0)
MyResults.Value = cell.Value
r = r + 1
End If
Next cell

myFin:
Sheets("Sheet3").Select
Sheets("Sheet3").Range("A1").Select

End Sub


--
DB100
------------------------------------------------------------------------
DB100's Profile:
http://www.excelforum.com/member.php...fo&userid=9176
View this thread: http://www.excelforum.com/showthread...hreadid=320116

Copy rows with a difference
 

Tom

Perfect - thanks

Davi

--
DB10
-----------------------------------------------------------------------
DB100's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=32011