For next loop and paste special Help please

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" 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(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
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=532116
>
>

Hi man thanks for the response

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

Dim cell As Range
On Error Resume Next
For Each cell In Sheets("L").Range("E:E").SpecialCells(xlCellTypeConstants)
If Application.WorksheetFunction.CountIf(Sheets("Last Week").Range("E:E"), cell.Value) > 0 Then
'do nothing
Else
Sheets("L").Range("A" & cell.Row & ":M" & cell.Row).Copy 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
Charles

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" wrote:

>
> Hi man thanks for the response
>
> When the code is modified the way you suggest the whole line of code
> turns red and it errors out and highlights the word Paste
>
> Dim cell As Range
> On Error Resume Next
> For Each cell In
> Sheets("L").Range("E:E").SpecialCells(xlCellTypeConstants)
> If Application.WorksheetFunction.CountIf(Sheets("Last
> Week").Range("E:E"), cell.Value) > 0 Then
> 'do nothing
> Else
> Sheets("L").Range("A" & cell.Row & ":M" & cell.Row).Copy
> 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
> Charles
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=532116
>
>

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

Thanks
Charles

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

"mrdata" wrote:

>
> I tried what you suggest but it gives me an error Invalid Charactor on
> the underscore
>
> 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
>
>

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

Charles

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.

"mrdata" wrote:

>
> Still errors out Invalid Charactor on the underscore with the space you
> suggested.
>
> Charles
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=532116
>
>