VBA-EXCEL-COPYING DIFFERENT ranges/cells at the same time to other workbook

Question

i can't figure out how to copy cells value(even by merged cells) between different workbook without use .activate. I know how to avoid select: dim x as range dim wrk1 as workbook dim wrk2 as workbook dim w as range dim r as range dim e as range 'etc...

but i can't copy ranges without using .activate .. Id like to get something working like:

sub avoid()

dim x as range
dim wrk1 as workbook
dim wrk2 as workbook
dim w as range
dim r as range 
dim e as range
set wrk1 = application.workbook("cartel1")   'already open workbook 
set wrk2 = workbook.open("workB")            'workbook was closed
set x = wrk1.range("a1")
set e = wrk1.range("a3")
set w = wrk2.range("a1")
set r = wrk2.range("a2")
x.copy(w)
e.copy(r)
end sub

but it doesn't work. Any help? thanks

R.Katnaan R.Katnaan · Accepted Answer · 2015-07-28T07:28:16

You made many mistakes. So, I modified it. I already tested the code. It work well for me. Try this one:

Public Sub avoid()

    Dim source1 As Range
    Dim source2 As Range

    Dim dest1 As Range
    Dim dest2 As Range

    Dim sourceBook As Workbook
    Dim destBook As Workbook

    Set sourceBook = Application.Workbooks("cartel1")

    'Here you need to set full path of new work book
    'Because new work book is same level with source work book, so I set => ThisWorkbook.Path & "\" & "workB"
    'Open destination work book

    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "workB"

    Set destBook = Application.Workbooks("workB")

    'Here, we can't get a Range from a workbook.
    'Only, we can get it from a worksheet. You forget to get sheet. I do it. Check.
    'You need to modify sheet names.

    Set source1 = sourceBook.Sheets("sourceSheet").Range("A1")
    Set source2 = sourceBook.Sheets("sourceSheet").Range("A3")

    Set dest1 = destBook.Sheets("destSheet").Range("A1")
    Set dest2 = destBook.Sheets("destSheet").Range("A2")

    source1.Copy dest1
    source2.Copy dest2

End Sub

VBA-EXCEL-COPYING DIFFERENT ranges/cells at the same time to other workbook

2 Answers