CODEWITHSUNDEEP
vbaexcel
user6996470
user6996470

Reputation:

copying ranges from workbooks (without folder path version)

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

Upvotes: 0

Views: 117

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2693

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

Upvotes: 1

Related Questions