[cool143]

Hi,

I wanted to create an macro application.

In 1st sheet, I have 5 columns and 1000 rows (may vary).
In 2nd sheet, I have 5 columns and 1000 rows (may vary).

Now I wanted to compare 1st sheet with 2nd sheet (Only one column)

If cells in sheet1 is not equal to sheet2, copy that entire row to new sheet3.

Please anyone help...

[JBeaucaire]

1) Is 1st sheet named Sheet1?
2) Is 2nd sheet named Sheet2?
3) Is 3rd sheet named Sheet3?

4) You want to compare ALL 5 cells in row1 on sheet1 to ALL 5 cells in row1 of sheet2? Then row2 on sheet1 to row2 on sheet2?

Or are the 5 cells on Sheet1 Row1 possibly found together somewhere other than row1 on sheet2? Meaning we need to check the entire dataset on sheet2?

5) Are there headers at the top of your 5 cells?

If you actually posted a sample sheet, this would be very easy to write. Click GO ADVANCED and use the paperclip icon to post up your workbook.

[cool143]

Hi

Please find the sample spread sheet.

1. I wanted to compare column D (Sheet1) with Column A(sheet2). If the cell is not matching, I wanted to copy the entire row of Sheet1 to sheet3.
2. When pasting the row in sheet3, I want some columns.
For eg: From sheet1, Column D,A,C ( that I highlighted in attached sheet - Sheet3)

Please help on this...

[JBeaucaire]

This little macro presumes column AA in sheet 1 is empty for a "filter" to be applied. It will put a formula in that column, then copy the rows that are "x", then remove the helper column.

Hopefully this will be faster than looping through the rows one at a time and testing the 3 criteria that way.

Give it a whirl:

Option Explicit

Sub Differences()
'JBeaucaire  (9/24/2009)
Dim LR As Long
Application.ScreenUpdating = False

Sheets("Sheet1").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row

Range("AA1") = "Key"
Range("AA2:AA" & LR).FormulaR1C1 = _
    "=IF(ISNUMBER(MATCH(RC4&""-""&RC1&""-""&RC2&""-""&RC3, INDEX(Sheet2!R2C1:R100C1&""-""&Sheet2!R2C2:R100C2&""-""&Sheet2!R2C3:R100C3&""-""&Sheet2!R2C4:R100C4,0), 0)),"""",""x"")"

Range("AA1").AutoFilter
Range("AA1").AutoFilter Field:=1, Criteria1:="x"

Sheets("Sheet3").Cells.Clear
Range("D1:D" & LR).Copy Sheets("Sheet3").Range("A1")
Range("A1:A" & LR).Copy Sheets("Sheet3").Range("B1")
Range("C1:C" & LR).Copy Sheets("Sheet3").Range("C1")
Range("AA1").AutoFilter
Range("AA1:AA" & LR).ClearContents
Sheets("Sheet3").Activate

Application.ScreenUpdating = True
End Sub

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.