title: Using ranges, how can I make a .COPY DESTINATION paste as VALUES
body: <p>I have the following code, which works just fine to copy the entire cells to their new destination.</p>
  
  <pre><code>Dim ws As Worksheet, rng As Range
  
  Set ws = Sheets("Duplicates")
  Set rng = ws.Range("A2")
  
  rng.Copy Destination:=Range("A2:A" &amp; Range("G" &amp; Rows.Count).End(xlUp).Row)
  </code></pre>
  
  <ol>
  <li>Is it possible to make that cope a <code>paste special</code>. </li>
  <li>Or do I
  need to split the copy/paste into two lines using a two range
  objects?</li>
  </ol>
  
  <p>EDIT:</p>
  
  <p>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.  </p>
  
  <p>If you copy a formula from Record 1 and paste it to all the records as <code>VALUES</code>, you will get the formula's value for the first record copied down. </p>
  
  <p>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.</p>
  
  <hr>
  
  <pre><code>Dim ws As Worksheet, rng As Range
  Set ws = Sheets("Duplicates")
  </code></pre>
  
  <p>Set up first range (single cell with formula) and copy/paste the contents down to the bottom of the spreadsheet based on Col <code>G</code>:</p>
  
  <pre><code>Set rng = ws.Range("A2")
  rng.Copy Destination:=Range("A2:A" &amp; Range("G" &amp; Rows.Count).End(xlUp).Row)
  </code></pre>
  
  <p>Select the freshly pasted formule, from the second row down.  Copy them.  Then paste the contents as values to the same place.</p>
  
  <pre><code>Set rng = ws.Range("A3:A" &amp; Range("G" &amp; Rows.Count).End(xlUp).Row)
  rng.Copy
  rng.PasteSpecial (xlPasteValues)
  Application.CutCopyMode = False 
  </code></pre>