0

I am trying to do a simple copy-and-paste value from one Excel file to another using Excel VBA. I have the following code but it seems to copy-and-paste over the format as well. How can I specify it so it does paste value only? Thanks.

Sub PasteValue()

Set x = Workbooks.Open("workbook name 1")

Workbooks("workbook name 1").Sheets("sheet name 1").Range("A1:M10").Copy Workbooks("workbook name 2").Sheets("sheet name 2").Range("A1")

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Henry Cao
  • 39
  • 1
  • 2
  • 7

3 Answers3

2

Alternatively, you can use:

Workbooks("workbook name 1").Sheets("sheet name 1").Range("A1:M10").Value = _
Workbooks("workbook name 2").Sheets("sheet name 2").Range("A1:M10").Value
Kubie
  • 1,551
  • 3
  • 12
  • 23
1

Just paste values using: 

Workbooks("workbook name 1").Sheets("sheet name 1").Range("A1:M10").Copy   
Workbooks("workbook name 2").Sheets("sheet name 2").Range("A1").PasteSpecial xlPasteValues
urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • After adding that, I am getting the following: "Compile error: Expected: end of statement" – Henry Cao Aug 15 '18 at 18:52
  • If you are referring to the correct book & correct sheet name with this code on 2 separate lines, there is no reason for this not work **unless** there is more code to your sub that you are not showing – urdearboy Aug 15 '18 at 18:55
0

Paste values only: try this

Sub Paste()

'Declare ranges
    Dim Source_Rg As Range 'Copy from source range
    Dim To_Rg As Range     'Past to target range

'Set source and target ranges
    'Source range
    Set Source_Rg = Workbooks("Source file name.xlsx") _
                   .Worksheets("Sheet name").Range("A1")

    'Target range (Active workbook)
    Set To_Rg = ActiveWorkbook.Worksheets("Sheet name") _
               .Range("A1")

'Paste values only
    To_Rg = Source_Rg

    Source_Rg = Nothing
    To_Rg = Nothing

End Sub
حسام هداية
  • 1
  • 2