VBA - Copy.Range to paste values only?

T

The Gent

Board Regular
Joined
Jul 23, 2019
Messages
50
Hi,

I am writing some code to copy and paste a range from one sheet to another but the code is bringing the formula through to the new destination whereas I only need the values.

Current code:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim Arange As Range, Drange As Range, Crange As Range, Trange As Range
Set Arange = ws.Range("A3:A" & lastrow)
Set Trange = ws.Range("B3:B" & lastrow)
Set Crange = ws.Range("D3:D" & lastrow)
Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & lastrow2)
Crange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("F" & lastrow2)
Trange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("T" & lastrow2)

How do I tweak this to transfer only values?
[/FONT]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try it like
Code: 
Arange.SpecialCells(xlCellTypeVisible).Copy
jnl.Range("C" & Lastrow2).PasteSpecial xlPasteValues
 
Upvote 0
Did you change this part of your code
Code: 
[FONT=Verdana]Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & lastrow2)[/FONT]
to what I suggested?
 
Upvote 0
In that case can you please supply the entire code.
When posting code please the code tags, the # icon in the reply window.
 
Upvote 0
Full code:

Code: 
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub PPEJnl()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Set ws = Sheet2
ws.AutoFilterMode = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ws.Cells.Clear[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim TTrng As Range
Set TTrng = Sheet18.Range("D33:D39")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim rng1 As Range, rng1a As Range, rng1b As Range, rng1c As Range[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Set rng1 = ws.Range("A3:A9")
Set rng1a = ws.Range("B3:B9")
Set rng1b = ws.Range("C3:C9")
Set rng1c = ws.Range("D3:D9")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]rng1.Value = 16000100
rng1a.Value = TTrng.Value
rng1b.Formula = "='PP&E'!F33"
rng1c.Formula = "='PP&E'!S33"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'Tidy up
lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Range("E3:E" & lastrow).Formula = "=ABS(C3)+ABS(D3)"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With ws.Range("A2:E" & lastrow)
    .AutoFilter field:=5, Criteria1:="<>0"
    .AutoFilter field:=3, Criteria1:="<0"
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim jnl As Worksheet
Set jnl = Sheet1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Lastrow2 = jnl.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim Arange As Range, Drange As Range, Crange As Range, Trange As Range
Set Arange = ws.Range("A3:A" & lastrow)
Set Trange = ws.Range("B3:B" & lastrow)
Set Crange = ws.Range("D3:D" & lastrow)
    Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & Lastrow2)
    Crange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("F" & Lastrow2)
    Trange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("T" & Lastrow2)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ws.AutoFilterMode = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]With ws.Range("A2:E" & lastrow)
    .AutoFilter field:=5, Criteria1:="<>0"
    .AutoFilter field:=3, Criteria1:=">0"
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Lastrow2 = jnl.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Set Arange = ws.Range("A3:A" & lastrow)
Set Trange = ws.Range("B3:B" & lastrow)
Set Drange = ws.Range("C3:C" & lastrow)
    Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & Lastrow2)
    Drange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("E" & Lastrow2)
    Trange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("T" & Lastrow2)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ws.Range("A1:A99999").Clear
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]
 
Upvote 0
:confused: You haven't made the changes I suggested
 
Upvote 0
Fluff said:
:confused: You haven't made the changes I suggested
Click to expand...

Apologies but this is the original. I had to take your suggestions out due to the error.

With them in it won't allow me to use the macro but here it is anyway...

Code: 
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub PPEJnl()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Set ws = Sheet2
ws.AutoFilterMode = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ws.Cells.Clear[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim TTrng As Range
Set TTrng = Sheet18.Range("D33:D39")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim rng1 As Range, rng1a As Range, rng1b As Range, rng1c As Range[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Set rng1 = ws.Range("A3:A9")
Set rng1a = ws.Range("B3:B9")
Set rng1b = ws.Range("C3:C9")
Set rng1c = ws.Range("D3:D9")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]rng1.Value = 16000100
rng1a.Value = TTrng.Value
rng1b.Formula = "='PP&E'!F33"
rng1c.Formula = "='PP&E'!S33"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'Tidy up
lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Range("E3:E" & lastrow).Formula = "=ABS(C3)+ABS(D3)"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With ws.Range("A2:E" & lastrow)
    .AutoFilter field:=5, Criteria1:="<>0"
    .AutoFilter field:=3, Criteria1:="<0"
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim jnl As Worksheet
Set jnl = Sheet1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Lastrow2 = jnl.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim Arange As Range, Drange As Range, Crange As Range, Trange As Range
Set Arange = ws.Range("A3:A" & lastrow)
Set Trange = ws.Range("B3:B" & lastrow)
Set Crange = ws.Range("D3:D" & lastrow)
    Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & Lastrow2).PasteSpecial xlPasteValues
    Crange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("F" & Lastrow2).PasteSpecial xlPasteValues
    Trange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("T" & Lastrow2).PasteSpecial xlPasteValues[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ws.AutoFilterMode = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]With ws.Range("A2:E" & lastrow)
    .AutoFilter field:=5, Criteria1:="<>0"
    .AutoFilter field:=3, Criteria1:=">0"
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Lastrow2 = jnl.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Set Arange = ws.Range("A3:A" & lastrow)
Set Trange = ws.Range("B3:B" & lastrow)
Set Drange = ws.Range("C3:C" & lastrow)
    Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & Lastrow2).PasteSpecial xlPasteValues
    Drange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("E" & Lastrow2).PasteSpecial xlPasteValues
    Trange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("T" & Lastrow2).PasteSpecial xlPasteValues[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ws.Range("A1:A99999").Clear
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]
 
Upvote 0
The lines you posted aren't the same as Fluff posted, for example
Code: 
Arange.SpecialCells(xlCellTypeVisible).Copy jnl.Range("C" & Lastrow2).PasteSpecial xlPasteValues
should be
Code: 
Arange.SpecialCells(xlCellTypeVisible).Copy 
jnl.Range("C" & Lastrow2).PasteSpecial xlPasteValues

i.e. separate lines and the same for all your similar lines.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Multiple filter criteria using VBA
Replies
2
Views
963
Thundercats
T
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
J
  • Question Question
VBA Search box and button code
Replies
0
Views
272
jlcodes
J
D
  • Question Question
Error Retrieving data with XLOOKUP after using VBA to copy to source cells.
Replies
6
Views
421
Alex Blakenburg
A
T
  • Question Question
Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"
Replies
1
Views
390
Tanquen
T

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top