For next loop and paste special Help please

[mrdata]

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(xlCellTypeConstants)
If
Application.WorksheetFunction.CountIf(Sheets("destination").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("destination")) + 1, 1)
End If

Next cell


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

Thanks
Charles

 

[Guest]

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("destination")) + 1,
1).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

.......

 

[mrdata]

Hi man thanks for the response

When the code is modified the way you suggest the whole line of cod
turns red and it errors out and highlights the word Paste

Dim cell As Range
On Error Resume Next
For Each cell I
Sheets("L").Range("E:E").SpecialCells(xlCellTypeConstants)
If Application.WorksheetFunction.CountIf(Sheets("Las
Week").Range("E:E"), cell.Value) > 0 Then
'do nothing
Else
Sheets("L").Range("A" & cell.Row & ":M" & cell.Row).Cop
Sheets("Last Week").Cells(LastRow(Sheets("Last Week")) + 1
1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End If

Next cell

the above is how I modified the code

Thanks
Charle

 

[Guest]

sounds like the code got wrapped to another line when pasted into the module.
the entire paste command should be on one line or, if wrapped to a second
line, you'll need to use the line continuation character (underscore)

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

 

[mrdata]

I tried what you suggest but it gives me an error Invalid Charactor on
the underscore

Thanks
Charles

 

[Guest]

Notice there is supposed to be a space in front of the underscore.

 

[mrdata]

Still errors out Invalid Charactor on the underscore with the space yo
suggested.

Charle

 

[Guest]

I pasted the code in exactly as you posted it earlier (fixed the lines
highlighted in red due to the forum wrapping them to another line) and it
works perfectly. Are all of the references to "Last Week" correct? I
noticed when I corrected one of the lines that came in wrong there was no
space in this worksheet name and it looks like there is supposed to be.

As I said, the code runs. This forum tends to wrap lines funny so I suspect
the issue is in copying/pasting the code from the post into a module.