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)
paste special
. 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
Select the cells that contain the data or other attributes that you want to copy. Click the first cell in the area where you want to paste what you copied. On the Home tab, under Edit, click Paste, and then click Paste Special. Paste all cell contents and formatting, including linked data.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With