For next loop and paste special Help please
 

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

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("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

For next loop and paste special Help please
 

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(xlCellTypeCo nstants)
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

--
mrdat
-----------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...fo&userid=1789
View this thread: http://www.excelforum.com/showthread.php?threadid=53211

For next loop and paste special Help please
 

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("destin ation")) + 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(xlCellTypeCo nstants)
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

For next loop and paste special Help please
 

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

For next loop and paste special Help please
 

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

For next loop and paste special Help please
 

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

Charle

--
mrdat
-----------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...fo&userid=1789
View this thread: http://www.excelforum.com/showthread.php?threadid=53211

For next loop and paste special Help please
 

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