Transfer Data from User Form to Multiple Worksheets in Excel Workbook Using VBA


How we can transfer data from a user-form to multiple worksheets in an Excel workbook using VBA quickly and easily.

A website visitor had the following query: ‘Sir, Can you help me on the sending the data from userform to next worksheet. The userform I have created sends input data to sheet 1, which calculates the scentific data displaying in specific cellls in sheet 1. A command button extracts this data both input and result in userform. This works perfectly and wants to proceed further.Now this complete data displayed on userform has to be send to sheet 2, everytime you send the data it has to be added to the next row in sheet 2. Can you advise the VB formula to do this.if you want I can send you the file to know what exactkly I am looking for?
regards
pilankar’

If we wish to transfer data from a user-form to an Excel worksheet we first need to know the number of rows already used in the Excel worksheet. The worksheet may just have headers if we are just starting our work or it may have many used rows from earlier manual data entry. So for every worksheet in which we wish to transfer data automatically from a user form, we need to know the number of rows already used.

Next we transfer the data from each text box, combo box, list box, etc step by step. to the relevant worksheet.

If automatic calculations are to be done, we need to take care of that situation also as shown in the video. The process is simple and data transfer happens instantaneously using VBA.

Let’s watch the video first:

Download sample file by clicking on the Excel icon:

Watch this video on YouTube

Here is the complete VBA code to transfer data from a user-form to relevant Excel worksheets:

Dim lrCD As Long, lrPD As Long, lrS As Long
lrCD = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).Row
Sheets(“CustomerDetails”).Cells(lrCD + 1, “A”).Value = TextBox1.Text
Sheets(“CustomerDetails”).Cells(lrCD + 1, “B”).Value = TextBox2.Text
Sheets(“CustomerDetails”).Cells(lrCD + 1, “C”).Value = TextBox3.Text
Sheets(“CustomerDetails”).Cells(lrCD + 1, “D”).Value = TextBox4.Text
Sheets(“CustomerDetails”).Cells(lrCD + 1, “D”).Columns.AutoFit

lrPD = Sheets(“ProductDetails”).Range(“A” & Rows.Count).End(xlUp).Row
Sheets(“ProductDetails”).Cells(lrCD + 1, “A”).Value = TextBox1.Text
Sheets(“ProductDetails”).Cells(lrCD + 1, “B”).Value = TextBox2.Text
Sheets(“ProductDetails”).Cells(lrCD + 1, “C”).Value = TextBox5.Text
Sheets(“ProductDetails”).Cells(lrCD + 1, “D”).Value = TextBox6.Text
Sheets(“ProductDetails”).Cells(lrCD + 1, “E”).Value = TextBox7.Text
Sheets(“ProductDetails”).Cells(lrCD + 1, “F”).Value = Val(TextBox6.Text) * Val
(TextBox7.Text)

lrS = Sheets(“ShipmentDetails”).Range(“A” & Rows.Count).End(xlUp).Row
Sheets(“ShipmentDetails”).Cells(lrCD + 1, “A”).Value = TextBox1.Text
Sheets(“ShipmentDetails”).Cells(lrCD + 1, “B”).Value = TextBox2.Text

Sheets(“ShipmentDetails”).Cells(lrCD + 1, “C”).Value = ComboBox1.Value
Sheets(“ShipmentDetails”).Cells(lrCD + 1, “D”).Value = TextBox8.Text
Private Sub UserForm_Initialize()
TextBox1.SetFocus
TextBox2.Text = Date
ComboBox1.List = Array(“By Air”, “By Land”)
End Sub