How to copy selection row from one Excel file to another

Question

I want to write a macro that will copy a selected range of cells from one Excel file to another. I tried that code but it doesn't work. The only action that I see is that wbk and wbk 1 workbooks are open, but the rest of code is not executed, so I can't copy what I need. Any ideas what is wrong?

Sub new()

    Dim wbk As Workbook
    Dim wbk1 As Workbook

    Set wbk = Workbooks.Open("C:\Users\Marcin\Desktop\plik zrodkowy.xlsm")
    Set wbk1 = Workbooks.Open("C:\Users\Marcin\Desktop\Zeszyt2.xlsm")

    ' now you can manipulate the data in the workbook anyway you want, e.g. '

    Dim x, y As Variant
    x = wbk.Worksheets("Sheet1").Range(Cells(1, 1), Cells(13, 2))
    y = wbk1.Worksheets("Sheet1").Range(Cells(1, 20), Cells(13, 23))

Your code just opens two workbooks and reads some data from them. What exactly did you want to do? — Tim Williams
@Marcin: The declaration Dim x, y As Variant is not what you seem to think it is: variable y is explicitly declared as Variant, but variable x defaults to Variant since there is no declaration of the variable type for the variable x. This is VBA and it's different than... If you would do the following Dim x, y As Double then y is "Double" type and x is still a Variant :-) Correct way is Dim x As Variant, y As Variant — Gene

Marcin Marcin · Accepted Answer · 2017-09-29T15:01:09

Thanks everyone for help,at the end I edit my code and now it works,like I need,here is my cod if it can help anyone in future:)I am developer from Poland so instead of "Arkusz1" you should use "Sheet1" if you are using english version of Excel. Regards Marcin

Dim strPath2 As String

Dim wbkWorkbook1 As Workbook
Dim wbkWorkbook2 As Workbook
Dim wbThis As Workbook

'define paths and filenames
strPath2 = "C:\Users\Marcin\Desktop\plik zrodkowy.xlsm"
Set wbThis = ThisWorkbook
'open files

Set wbkWorkbook2 = Workbooks.Open(strPath2)
Set Workbook1 = ThisWorkbook
'copy the values across
'### change the sheet and range to what you need
'wbkWorkbook2.Worksheets("Arkusz1").Range("A1:B3").Value = _
    wbkWorkbook1.Worksheets("Arkusz1").Range("A1:B3").Value'

 wbThis.Worksheets("Arkusz1").Range("E1:F3").Value = 
 wbkWorkbook2.Worksheets("Arkusz1").Range("A1:C3").Value

'close the workbook
wbkWorkbook2.Close (True)

How to copy selection row from one Excel file to another

2 Answers