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

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

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

myData.Save

End Sub

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



Watch the video on youtube