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?

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

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()
TextBox2.Text = Date
ComboBox1.List = Array(“By Air”, “By Land”)
End Sub

17 thoughts on “Transfer Data from User Form to Multiple Worksheets in Excel Workbook Using VBA

  1. faiyaz ahmed

    It’s a very useful. I tried it. Thanks for providing the vba code.
    I need the sample project in which subject wise marks enter.I tried that but the percentage is not displayed. Can you give link for that project vba code. Thanks in advance

  2. Shakil Arvi

    i want to use checkbox if yes then store today date in excel cell. If i received the payment then i will click the checkbox and in excel sheet store today date, therefore we know that we received the payment on that date.


    shakil arvi

  3. Nagesh Patil

    Dear sir,
    i trying to make a agri shop application using excel vba but i have not ideas to complete it. can i send to you for better solution.

  4. Dipesh

    Namaste sir,

    First of all thanking you very much sir i have learn lots of things from you site.

    i am working with school sir and i have make one user form to enter data but one user can use one time only.

    sir it is possible that we have single excel sheet data and have multiple user entry form ?
    like 4 teacher can enter mark same time in one excel file from network sharing ?

    and if i want enter students mark i can ? like first i enter 500 student data with name, class and section then i search class section and feel other 6 subject mark in columns ?

  5. Swk8429

    Hi, thanks for the great Still do you have/know of a way a user can find the records via the invoice no so that the info appears in the respective textbox/combo boxs. And likewise the code to update them. Thanks

  6. Swk8429

    Hi, thanks for the great video. do you have/know of a way a user can find the records via the invoice no so that the info appears in the respective textbox/combo boxs. And likewise the code to update them. Thanks

  7. Radesh

    i wrote the following code but this always enters in the first row only. and replacing the old entry

    Private Sub frmPaperData_Initialize()

    txtFamily.Value = “”
    txtSubstrate.Value = “”
    txtFinish.Value = “”
    txtL.Value = “”
    txtW.Value = “”
    txtgsm.Value = “”
    txtPacking.Value = “”
    txtWtPPkt.Value = “”
    txtCostPPkt.Value = “”
    txtCostPSheet.Value = “”
    txtFamily.Text = “”

    End Sub

    Private Sub cmdCancel_Click()

    Unload Me

    End Sub

    Private Sub cmdClear_Click()

    Call frmPaperData_Initialize

    End Sub

    Private Sub cmdSave_Click()

    Dim lrPD As Long

    lrCD = Sheets(“PaperData”).Range(“A” & Rows.Count).End(xlDown).Row + 1
    Sheets(“PaperData”).Cells(lrPD + 1, “A”).Value = Val(TextBox1.Text)
    Sheets(“PaperData”).Cells(lrPD + 1, “B”).Value = txtFamily.Text
    Sheets(“PaperData”).Cells(lrPD + 1, “C”).Value = txtSubstrate.Text
    Sheets(“PaperData”).Cells(lrPD + 1, “D”).Value = txtFinish.Text
    Sheets(“PaperData”).Cells(lrPD + 1, “E”).Value = Val(txtL.Text)
    Sheets(“PaperData”).Cells(lrPD + 1, “F”).Value = Val(txtW.Text)
    Sheets(“PaperData”).Cells(lrPD + 1, “G”).Value = Val(txtgsm.Text)
    Sheets(“PaperData”).Cells(lrPD + 1, “H”).Value = Val(txtPacking.Text)
    Sheets(“PaperData”).Cells(lrPD + 1, “I”).Value = (Val(txtL.Text) * Val(txtW.Text) * Val(txtgsm.Text) * Val(txtPacking.Text)) / 1000
    Sheets(“PaperData”).Cells(lrPD + 1, “J”).Value = Val(txtCostPPkt.Text)
    Sheets(“PaperData”).Cells(lrPD + 1, “K”).Value = Val(txtCostPSheet.Text)
    End Sub

  8. Botrous

    Hi, Can I use Userform to post in ( excel File1 ) and also post in ( excel File2 )or ( excel File3 ) or ( excel File4) as per the selection I choose in that form.
    I need to link the user form with different files to keep everything categorize

  9. kpratt

    I have a workbook with 30 different sheets (account numbers). I want to have my user form take the account number from a combo list box. Depending on the account number selected have the user from transfer the data into the next cells on the worksheet that corresponds to the account number selected.

  10. Abdul karim

    sir same question kpratt
    i have many client debit credit sheets i need add data from through one user form when select customer from combobox then put data and then press submit then past on specific customer sheets and i need to add one more thing
    is it possible when i get new client and i want to need create for new client same dabit cradite sheet can i add new new debit credit sheet from vba user form ?

  11. Giselle

    Dear Dinesh,
    Just wanted to thank you for your valuable videos and explanations, thanks to you I had a lot of work done and learn so much about VBA, I never imagine could be so easy to learn it. Your site and videos are just the best all over the internet.
    Thank you again.

  12. William Ooi

    Hi Sir,

    I follow your coding but I having this error when i click add button:
    vba runtime error 9 subscript out of range

    Private Sub CommandButton1_Click()
    Dim lrlaptop As Integer

    lrlaptop = Sheets(“LaptopAsset”).Range(“A” & Rows.Count).End(xlUp).Row <—— Debug code
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "A").Value = pcName.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "B").Value = username.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "c").Value = uid.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "D").Value = model.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "E").Value = assetNumber.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "F").Value = financeNumber.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "G").Value = requestor.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "H").Value = cc.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "I").Value = cartNumber.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "J").Value = purchaseOrder.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "K").Value = deliveryOrder.Text
    Sheets("LaptopAsset").Cells(lrlaptop + 1, "M").Value = supervisor.Text

    End Sub

    Private Sub UserForm_Initialize()
    deliveryOrder.Text = Date
    D_DO.Text = Date
    End Sub

    Anything wrong ???

  13. Jeet Shah


    I was exactly looking for something like this but there’s a bit customization.

    I’ll have around 6-7 data fields with the last field being a drop down menu in the user form.The drop down menu will have four different options as PA, TRF, APPROVED, HIGHPAY

    There will be four different sheets named as PA, TRF, APPROVED, HIGHPAY. I would want that on the basis of drop down option selected, the data from the user form should be posted to that respective sheet.

    Also, PA is a stage before approval which gets involved a few times. Is it possible that the initial data which was posted to PA can be trf to APPROVED using some other form (along with it being erased from the PA tab).

    Thank you.


  14. Glass

    I have used your instruction to create a form that writes data to 2 sheets within the same workbook. The form resides on sheet1. when sending the date field to sheet2, I get a type mismatch. any assistance would be helpful.


Leave a Reply

Your email address will not be published. Required fields are marked *