Using ranges, how can I make a .COPY DESTINATION paste as VALUES

Question

I have the following code, which works just fine to copy the entire cells to their new destination.

Dim ws As Worksheet, rng As Range

Set ws = Sheets("Duplicates")
Set rng = ws.Range("A2")

rng.Copy Destination:=Range("A2:A" & Range("G" & Rows.Count).End(xlUp).Row)

1. Is it possible to make that cope a paste special.
2. Or do I need to split the copy/paste into two lines using a two range objects?

EDIT:

Right, we've concluded that you CANNOT have a paste special on the same line as a copy. What I've also found is that you may not wish to anyway.

If you copy a formula from Record 1 and paste it to all the records as VALUES, you will get the formula's value for the first record copied down.

This is not what I wanted. So you have to copy the formulae down to all the records as a formulae, then copy those formula and paste as values.

---

Dim ws As Worksheet, rng As Range
Set ws = Sheets("Duplicates")

Set up first range (single cell with formula) and copy/paste the contents down to the bottom of the spreadsheet based on Col G:

Set rng = ws.Range("A2")
rng.Copy Destination:=Range("A2:A" & Range("G" & Rows.Count).End(xlUp).Row)

Select the freshly pasted formule, from the second row down. Copy them. Then paste the contents as values to the same place.

Set rng = ws.Range("A3:A" & Range("G" & Rows.Count).End(xlUp).Row)
rng.Copy
rng.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False 

Answer 1

Alternatively, you can just assign range values without using copy & paste:

Set r1 = Range("D1", "D7")
Set r2 = Range("E1", "E7")

r2.Value = r1.Value ' copies current values from one range to another

after this assignment, the r2 range will have values not formulas even if r1 has forumlas.

In your case, perhaps:

rng.Value = rng.Value

(once you've set rng to the correct size as you've noted)