Copy from one workbook and paste into another

by

This should do it, let me know if you have trouble with it:

Sub foo()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, copy what you want from x:
x.Sheets("name of copying sheet").Range("A1").Copy

'Now, paste to y worksheet:
y.Sheets("sheetname").Range("A1").PasteSpecial

'Close x:
x.Close

End Sub

Alternatively, you could just:

Sub foo2()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1") 

'Close x:
x.Close

End Sub

To extend this to the entire sheet:

With x.Sheets("name of copying sheet").UsedRange
    'Now, paste to y worksheet:
    y.Sheets("sheet name").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With

And yet another way, store the value as a variable and write the variable to the destination:

Sub foo3()
Dim x As Workbook
Dim y As Workbook
Dim vals as Variant

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Store the value in a variable:
vals = x.Sheets("name of sheet").Range("A1").Value

'Use the variable to assign a value to the other file/sheet:
y.Sheets("sheetname").Range("A1").Value = vals 

'Close x:
x.Close

End Sub

The last method above is usually the fastest for most applications, but do note that for very large datasets (100k rows) it’s observed that the Clipboard actually outperforms the array dump:

Copy/PasteSpecial vs Range.Value = Range.Value

That said, there are other considerations than just speed, and it may be the case that the performance hit on a large dataset is worth the tradeoff, to avoid interacting with the Clipboard.

More Related Contents:

  1. VBA comma separated cells, needt last entry only
  2. What is the default scope of worksheets and cells and range?
  3. Convert an entire range to uppercase without looping through all the cells
  4. Are there disadvantages in putting code into Userforms instead of modules?
  5. Save each sheet in a workbook to separate CSV files
  6. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  7. Excel: How to check if a cell is empty with VBA? [duplicate]
  8. ShowAllData method of Worksheet class failed
  9. Detect if range is empty
  10. Checking if a worksheet-based checkbox is checked
  11. Assign keyboard shortcut to run procedure
  12. Uncheck all checkboxes across entire workbook via CommandButton
  13. return csv file as recordset
  14. VBA workbooks.Close without being prompted to if the user wants to save?
  15. Get text from clipboard using GetText – avoid error on empty clipboard
  16. Copy a selected range to another worksheet
  17. Benchmarking VBA Code
  18. Excel macro – paste only non empty cells from one sheet to another
  19. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  20. Pointers needed for speeding up nested loop macro in VBA
  21. Display milliseconds in Excel
  22. ‘1004’: “The sort reference is not valid.”
  23. Application.Quit command not closing the entire Excel Application
  24. Create hyperlink to another sheet
  25. Trying to open the workbook in separate instance
  26. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  27. Create folder path if does not exist (saving from VBA)
  28. Access files with long paths (over 260)
  29. Concatenate multiple ranges using vba
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment