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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
823
Thundercats
T
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
263
rlv01
R
J
  • Question Question
VBA Search box and button code
Replies
0
Views
247
jlcodes
J
D
  • Question Question
Error Retrieving data with XLOOKUP after using VBA to copy to source cells.
Replies
6
Views
389
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
348
Tanquen
T

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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