Copy contents only

[Metrazal]

Trying to copy "contents only" using the following code.
Having errors.. etc.. Any help??


Worksheets("Invoice").Range(2, "A:M").Copy
Destination = Worksheets("Master").Range(7, "A:M")


Thanks,

Met

[Dave Peterson]

What did you want to copy A2:M2?

Worksheets("Invoice").Range("a2:m2").Copy
worksheets("master").range("a7:m7").pastespecial paste:=xlpastevalues

or

worksheets("master").range("a7:m7").value _
= worksheets("invoice").range("a2:m2")



Metrazal wrote:
>
> Trying to copy "contents only" using the following code.
> Having errors.. etc.. Any help??
>
> Worksheets("Invoice").Range(2, "A:M").Copy
> Destination = Worksheets("Master").Range(7, "A:M")
>
> Thanks,
>
> Met
>
> --
> Metrazal
> ------------------------------------------------------------------------
> Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
> View this thread: http://www.excelforum.com/showthread...hreadid=518444

--

Dave Peterson

[Metrazal]

Thats it..

Thanks,

Met

[Metrazal]

I adjusted the code to allow multiple fields. However, I have an error. Wheres my problem?


For i = 1 To 9999
Worksheets("Invoice").Range(i, "a2:m2").Copy
Worksheets("master").Range(i, "a7:m7").PasteSpecial Paste:=xlPasteValues
Next i


Thanks,

Met

[Dave Peterson]

This doesn't work:
..Range(i, "a2:m2")

Are you trying to copy A2:M10000 to A7:M10006?

If yes, then just do it all at once:

Worksheets("Invoice").Range("a2:m10000").Copy
worksheets("master").range("a7").pastespecial paste:=xlpastevalues

Excel is smart enough to resize the destination range to match the copied range.

or

Dim RngToCopy as range
dim DestCell as range
set rngtocopy = worksheets("invoice").range("a2:m10000")
set destcell = worksheets("master").range("a7")

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


Metrazal wrote:
>
> I adjusted the code to allow multiple fields. However, I have an error.
> Wheres my problem?
>
> For i = 1 To 9999
> Worksheets("Invoice").Range(i, "a2:m2").Copy
> Worksheets("master").Range(i, "a7:m7").PasteSpecial
> Paste:=xlPasteValues
> Next i
>
> Thanks,
>
> Met
>
> --
> Metrazal
> ------------------------------------------------------------------------
> Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
> View this thread: http://www.excelforum.com/showthread...hreadid=518444

--

Dave Peterson

[Metrazal]

That did it..

Thanks again,
Met

[Tim Barlow]

Met,

Not sure what your trying to do with Range(2,"A:M") - do you mean
Range("A2:M2")?

Your code, if corrected, will copy the entire cells (formula, formats etc).

If you want to copy just the cell values, easiest way (rather than using
paste special) is to use a temporary array to store the values:

Dim tmpArr As Variant

tmpArr = Worksheets("Invoice").Range("A2:M2")
Worksheets("Master").Range("A7:M7") = tmpArr


HTH

Tim


"Metrazal" <[email protected]> wrote in
message news:[email protected]...
>
> Trying to copy "contents only" using the following code.
> Having errors.. etc.. Any help??
>
>
> Worksheets("Invoice").Range(2, "A:M").Copy
> Destination = Worksheets("Master").Range(7, "A:M")
>
>
> Thanks,
>
> Met
>
>
> --
> Metrazal
> ------------------------------------------------------------------------
> Metrazal's Profile:
http://www.excelforum.com/member.php...o&userid=31648
> View this thread: http://www.excelforum.com/showthread...hreadid=518444
>