copying ranges from workbooks (without folder path version)

0
votes

I've recently been looking for ways to speed up copying data from one worksheet to another. And I came across this nice piece of code (however this was posted in 2013).

Could you please help? I don't want to specify any path to workbooks (like in the example below). I have both worksheets open and would like to address them by filename.

I've tried changing "workbooks.open" to "window("xxx").activate" but that doesn't work.

thank you!

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 ")


x.Sheets("name of copying sheet").Range("A1").Copy
y.Sheets("sheetname").Range("A1").PasteSpecial

End Sub

2
vbaexcel
Do you want a code to copy workbooks that are already open? However, you'll need to specify names of workbooks to work with them. You'll also need to specify sheet names if your workbooks have more than one. – PedroMVM

2 Answers

1
votes
Sub foo()

    Dim x As Workbook
    Dim y As Workbook

    'Replace the text between the "" with the exact name of the workbook
    Set x = Workbooks("ActualNameOfWorkBook.xls")
    Set y = Workbooks("ActualNameOfOtherWorkBook.xls")


    x.Sheets("name of copying sheet").Range("A1").Copy
    y.Sheets("sheetname").Range("A1").PasteSpecial

End Sub
1
votes

When using PasteSpecial you need to add the XlPasteTypewhat (what parameter/s from the copied range you want to use). Some options of XlPasteTypewhat are: xlPasteAll , xlPasteFormulas, xlPasteValues etc.

You can read more about it at MSDN.

In the example below I am using xlPasteAll.

Code 

Sub foo()

Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open("file_name_x.xslx") '<-- don;t forget to add the extension, .xslx or .xlsm
Set y = Workbooks.Open("file_name_y.xslx") '<-- don;t forget to add the extension, .xslx or .xlsm

x.Sheets("name of copying sheet").Range("A1").Copy
y.Sheets("sheetname").Range("A1").PasteSpecial xlPasteAll '<-- add parameter after the PasteSpecial 

End Sub