ActiveSheet.Paste type (Paste Only values)

Question

I have this simply code, I want to copy only values from WoorkBook1 into Workbook2, only values, because Workbook2 have a specific format.

Sub Test()
   Workbooks("Workbook1.xlsx").Activate
   Range("B2:B7").Select
   Selection.Copy
   Workbooks("Workbook2.xlsx").Activate
   Range("B5:B10").Select
   ActiveSheet.Paste
End Sub

Tnx4all

score 5 · Accepted Answer · answered Jun 02 '15 at 12:24

5

Use PasteSpecial with the xlPasteValues option

Sub Test()
   Workbooks("Workbook1.xlsx").Activate
   Range("B2:B7").Select
   Selection.Copy
   Workbooks("Workbook2.xlsx").Activate
   Range("B5:B10").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

answered Jun 02 '15 at 12:24

moffeltje

4,521
4
33
57

I will do it in 6 min exactly! :) – Jun 02 '15 at 12:29

score 0 · Answer 2 · edited Jun 03 '21 at 16:55

0

A more efficient way of doing it:

Sub Test()

Workbooks("Workbook1.xlsx").Activate
Range("B2:B7").Copy
Workbooks("Workbook2.xlsx").Activate
Range("B5:B10").PasteSpecial Paste:=xlPasteValues

End Sub

edited Jun 03 '21 at 16:55

Adrian Mole

49,934
160
51
83

answered Jun 03 '21 at 10:00

Samidare

1

ActiveSheet.Paste type (Paste Only values)

2 Answers2