At times you may wish to open a workbook, add a couple of items from the workbook you are working in to a list and close the workbook. This type of update might be done on a specific set of cells and the results are added to the bottom of a list in a destination workbook.
Let's say we have data in Cells B10:B11 and we want to update our master workbook.
In cell B9 I have a path:
B9 = D:\Example1.xlsx
So in my D drive I have an Excel file called Example 1. The idea is to push 2 cells of data (it may be any number of cells) from the sheet you are working on into the Example1 workbook. Things to remember when building your version of the model.
Make sure the cells you wish to push from one file to the other are in B10 and B11. The Excel VBA to get the job done is as follows:
Option Explicit
Sub OpenMove()
Dim wb As Workbook
Dim ar(1 To 2) As String
ar(1) = [B10]
ar(2) = [B11]
Set wb = Workbooks.Open([B9]) ' Location B9 of the other workbook.
Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 2) = ar
wb.Close True 'Save and close
End Sub
The declaration of the workbook is made and the wb is open from the cells in B9 which contains a valid path and file name.
The destination path is resized with the following line.
Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 2)
So Excel picks up the last cell (let's say the last used cell is row 11 (1 is added to this to make it row 12 the first blank row). Now the range is resized to be 2 columns wide (.resize(,2)). The contents of the array are put in this 2 cell destination.
The file is then closed with the changes saved.
Don't use this method in Excel if you have lots of cells to populate. There are far more efficient methods of transferring data from one file to another. The following Excel file outlines this method. Make sure you have a valid file path and data in the relevant cells.
