How to Transfer Data from One Excel Workbook to Another Automatically

Home More Excel (VBA) Training Videos

You can use Excel VBA or Visual Basic for Applications to transfer data from one Excel workbook to another automatically.
The process involves the following steps:
  • Create two workbooks - (a)enterData and (b) Postings in a folder. Of course, you can have them in different folders also, if you like
  • Now create your macro attached to a command button
  • The macro whose complete code is given below will automate the process
Macro code:
Private Sub CommandButton1_Click()
Dim itemName As String
Dim itemPrice As Single
Dim myData As Workbook

itemName = Range("B1")
'Worksheets("sheet1").Select - this line may not be necessary
itemPrice = Range("B2")

Set myData = Workbooks.Open("C:\Stock\Postings.xlsx")
RowCount = Worksheets("sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0) = itemName
.Offset(RowCount, 1) = itemPrice
End With


End Sub

Watch the Excel training video below  to see the macro in action:

Watch the video on youtube