Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seperating Rows By Price Difference | New Users to Excel | |||
Copy Entire rows with a difference | Excel Programming | |||
Copy Entire rows with a difference | Excel Programming | |||
Copy Entire rows with a difference | Excel Programming | |||
VBA code to calculate the difference between rows | Excel Programming |