I think you just need to change the copy statement slightly

.....
Sheets("Source").Range("A" & cell.Row & ":M" & cell.Row).Copy

Sheets("destination").Cells(LastRow(Sheets("destin ation")) + 1,
1).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

.......

"mrdata" wrote:


This sub works Thanks to Mr Ron de Bruin
But I need to preserve the destination worksheet formating How do I
modify this ?


Dim cell As Range
On Error Resume Next
For Each cell In
Sheets("Source").Range("E:E").SpecialCells(xlCellT ypeConstants)
If
Application.WorksheetFunction.CountIf(Sheets("dest ination").Range("E:E"),
cell.Value) 0 Then
'do nothing
Else
Sheets("Source").Range("A" & cell.Row & ":M" &
cell.Row).Copy
Sheets("destination").Cells(LastRow(Sheets("destin ation")) + 1, 1)
End If

Next cell


I need to use paste special xlvalues I think
but how?

Thanks
Charles


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=532116