Working with Multiple Open Workbooks

This post is only about scenario when workbooks, to work with, are open. If any workbook remains close then "Subscript out of range" error will be thrown.

Consider two workbooks:

destination.xlsm : where data will be pasted. Here data is values in two cells - for simplicity.
source.xlsm: cell values will be copied from sheet1 of this workbook.

Code:

Declaration and setting of two workbook variables:

Dim wbRead As Workbook
Set wbRead = Workbooks("source.xlsx")
Dim wbWrite As Workbook
Set wbWrite = Workbooks("destination.xlsx")


Copy paste values from cells A1 and B1 of source to A1 and A2 of destination.

wbWrite.Worksheets(1).Range("A1") = wbRead.Worksheets(1).Range("A1")
wbWrite.Worksheets(1).Range("A2") = wbRead.Worksheets(1).Range("B1")

Save and Close destination workbook:

wbWrite.Save

wbWrite.Close