May 30, 2014

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

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

  1. 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. Thanks sir,

    I am looking for a vba code for below task.

    copy entire row to another sheet if J cell is not empty.

  3. 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

      1. sir,
        i am try to make allocation sheet, i have made userforms with rowsource, userforms shows the list
        of names which i want to send selected name to selected cell, and i have another problem some cells are merged. please help me.

  4. 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.

  5. 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 ?

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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.

  11. 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 ?

  12. 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.

  13. 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 ???

    1. I am also getting the same error as “subscript out of range”. what was the wrong?

  14. Hi,

    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.


  15. 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.

  16. Hi Sir,
    Is it possible to retrieve and edit data from multiple worksheets to a single user form with a unique ID to compare using Excel VBA?. If you have done this before on your video please send me a link on this.

    Thank you.

  17. Hi,

    I tried this code and other similar ones.
    Always encounter error on the below code (error 9).

    lrCD = Sheets(“Client”).Range(“F” & Rows.Count).End(xlUp).Row

    how to debug this code?
    I wish to transfer data from user form to different sheet. is there other way to do it?

    I’m using Microsoft excel 2016 by the way.

  18. I have a userform that collects customer information and generates a receipt. Presently the receipt is generated in another tab within the same workbook. Each time the userform is used the receipt is over written.
    I would like to have the receipt put into another workbook and each receipt is put in a new tab at the end. So, if the form is run 10 tens the other workbook has 10 tabs each containing a different receipt.

  19. Hi sir,

    My data is contains day wise data and i need to consolidate into weekly wise week 1consolidation, week 2 consolidation. I need to give date range in particular month to create it. please help

  20. Sir
    “How to transfer data from a user form to multiple worksheets in a workbook in Excel using VBA”, when I use this code, I got Run-Time error’9′: “Subscript out of Range”, and it will not execute the programme.
    Kindly Debug this error at your earliest,

  21. Hi to all,
    i had a urgent request related this topics. i need a user form same like this but i have to send all data in a one sheet not multiple sheet. and in user form i need only one page user form. and also i need a image field also in user form where i can upload a image from other directory and send it to same excel database .please anybody here to help me. i need it urgent.

    waiting for your quick support

  22. Sir, I want make easy billing software, because I am a shopkeeper. After making invoice the same each qty out from stock sheet. I am able to make invoice userform but not able to out each quantities from stock sheet by exc vba

  23. I have a similar issue, mine is to send the data to different workbook not worksheet. Please me with a code for that.

Comments are closed.