Compare two columns in two different workbooks

[Keenman]

I have two workbooks. One named "last.xls" and the other name
"new.xls". Both have one sheet each with a SheetName of "Sheet1"

Both sheets have the same number of columns (A thru K). Here is what
need to do with the data:

I want to compare "Column A" in both workbooks. If a value exists i
"Column A of last.xls", but does not exist in "Column A of new.xls",
want to take that entire row (the one that exists in last.xls, but no
in new.xls) and copy it to a new workbook named tnnew.xls. I need t
copy all of the rows in "last.xls" that do not exist in "new.xls" an
copy them to the new workbook (tnnew.xls)

I need the formating to remain the same in the new workbook as it is i
last.xls. I would also like to copy the header row from "last.xls" t
"tnnew.xls" (The header row is always row 1 Cells A thru K)

I don't really know where to start with this. I've tried some VLOOKU
formulas to no avail. I assume that a VBA module may be the best way t
go.

Any help is greatly appreciated.


Thank

 

[MSP77079]

I haven't tested this code, but it should work:

Sub comparecolumns()

LastOld = Workbooks("last.xls").Cells.SpecialCells(xlLastCell).Row
LastNew = Workbooks("new.xls").Cells.SpecialCells(xlLastCell).Row
LastRow = LastOld
If LastNew > LastOld Then LastRow = LastNew

Workbooks("last.xls").Rows("1:1").Copy _
Destination:=Workbooks("tnnew.xls").Range("A1")

For i = 2 To LastRow
If Workbooks("new.xls").Cells(i, "A") = "" Then
Workbooks("last.xls").Rows(i & ":" & i).Copy _
Destination:=Workbooks("tnnew.xls").Range("A" & i)
Else:
Workbooks("new.xls").Rows(i & ":" & i).Copy _
Destination:=Workbooks("tnnew.xls").Range("A" & i)
End If
Next i

End Sub

 

[Keenman]

MSP77079,

I'm a novice, so I'm sure I did something wrong here when I tried you
routine.

I tried it a couple of ways. First I put your code in a new workboo
(assuming that last.xls and new.xls would have to be open in Excel).
Doing it this way, I get subscript out of range (when I debug, it is o
the first line of code).

Second time around, I put the code in a module in "last.xls". When
run it then I get "Object doesn't support this property or method"
(again when I run debug it is on the first line of code).

Any ideas what I am doing wrong?


Thanks agai

 

[Myrna Larson]

Cells is a property of a worksheet, not a workbook. I think it should be
something like this. Change worksheet name as appropriate. Do the same in the
2nd line.

LastOld =
Workbooks("last.xls").Worksheets("Sheet1").Cells.SpecialCells(xlLastCell).Row

 

[Cecilkumara Fernando]

Keenman,
Dose the "last.xls" has formulas?
Do any of the data sets have duplicate entries within that data range?
How big the data sets are?
Open both "last.xls" and "new.xls".
Put this code in "last.xls" general module, and save it
and run the macro1
Cecil

Sub Macro1()
Dim LRow As Long
Dim StRow As Long
Dim i As Long

Sheets("Sheet1").Select
Sheets("Sheet1").Copy

'Change the path to suit

ActiveWorkbook.SaveAs _
Filename:="C:\My Documents\ExcelFiles\tnnew.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("L2").Formula = "=Row()"
Range("M2").Formula = "=""lAst"" & L2"
Range("L2:M2").AutoFill _
Destination:=Range("L2:M" & LRow), Type:=xlFillDefault
StRow = LRow + 1
Range("A" & StRow).Select
Windows("new.xls").Activate
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:K" & LRow).Copy
Windows("tnnew.xls").Activate
ActiveCell.PasteSpecial
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("L" & StRow).Formula = "=Row()"
Range("M" & StRow).Formula = "nEw"
Range("L" & StRow & ":M" & StRow).AutoFill _
Destination:=Range("L" & StRow & ":M" & LRow), _
Type:=xlFillDefault
With Range("L1:M" & LRow)
..Copy
..PasteSpecial xlPasteValues
End With
Range("A1:M" & LRow).Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("M2"), Order2:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
For i = LRow To 2 Step -1
If Range("A" & i) = Range("A" & i - 1) Then
Range("A" & i).EntireRow.Delete
End If
If Range("M" & i) = "nEw" Then
Range("M" & i).EntireRow.Delete
End If
Next i
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:M" & LRow).Sort _
Key1:=Range("L2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
'Range("L1:M1").EntireColumn.Delete
'Range("A1").Select
End Sub