VBA .copy destination

I have this code:

Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")

doing it this way copies the formatting as well, is there a way to copy just the value over without the cell format?

Thanks in advance

One way...

dim RngToCopy as range
Dim DestCell as range

set rngtocopy = worksheets("Sheet1".range("a1:d4")
set destcell = worksheets("sheet2").range("E5")

destcell.resize(rngtocopy.rows.count,rngtocopy.columns.count).value _
= rngtocopy.value

========
Another way is to just copy|paste special|values

dim RngToCopy as range
Dim DestCell as range

set rngtocopy = worksheets("Sheet1".range("a1:d4")
set destcell = worksheets("sheet2").range("E5")

rngtcopy.copy
destcell.pastespecial paste:=xlpastevalues



jerredjohnson wrote:
>
> I have this code:
>
> Worksheets("Sheet1").Range("A1:D4").Copy _
> destination:=Worksheets("Sheet2").Range("E5")
>
> doing it this way copies the formatting as well, is there a way to copy
> just the value over without the cell format?
>
> Thanks in advance
>
> --
> jerredjohnson
> ------------------------------------------------------------------------
> jerredjohnson's Profile: http://www.excelforum.com/member.php...o&userid=32236
> View this thread: http://www.excelforum.com/showthread...hreadid=562642

--

Dave Peterson

to copy just the values you don't need to copy. try
Worksheets("Sheet2").Range("E5:h9").value= _
Worksheets("Sheet1").Range("A1:D4").value
ranges must be the same size
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"jerredjohnson" <jerredjohnson.2b5uch_1153258508.578@excelforum-nospam.com>
wrote in message
news:jerredjohnson.2b5uch_1153258508.578@excelforum-nospam.com...
>
> I have this code:
>
> Worksheets("Sheet1").Range("A1:D4").Copy _
> destination:=Worksheets("Sheet2").Range("E5")
>
> doing it this way copies the formatting as well, is there a way to copy
> just the value over without the cell format?
>
> Thanks in advance
>
>
> --
> jerredjohnson
> ------------------------------------------------------------------------
> jerredjohnson's Profile:
> http://www.excelforum.com/member.php...o&userid=32236
> View this thread: http://www.excelforum.com/showthread...hreadid=562642
>