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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

N
  • Question
My code is suddenly really slow
Replies
1
Views
125
RobP
R
A
  • Locked
  • Solved
Edit the code to Multiple Columns
Replies
2
Views
104
Fluff
Fluff
E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
214
Joe4
Joe4
I
  • Question
VBA to filter, copy and paste in separate workbook
Replies
0
Views
514
Iron_Man
I
C
  • Question
Copy and paste data through multiple workbooks
Replies
2
Views
173
CDaviess
C

Forum statistics

Threads
1,214,376
Messages
6,119,180
Members
448,871
Latest member
hengshankouniuniu

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