Announcement

Collapse
No announcement yet.

Copy data from one workbook to another using Userform input

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy data from one workbook to another using Userform input



    Description
    I have an excel datasheet contain a recordset of the product data imported from ms access,I am using userform to search the data and if found paste the data on the target workbook. So far I got the way but to copy paste data, but my boss requirement is quite difficult to me cause Iím completely new to the excel vba.

    My department is a carried out test on the product and I need to produce kind of report generator by using the data. The test contain two types pre and post test, the post test is using the same product name as they used for pre test. the target workbook have some templates in it

    Problem
    1. So far I got the copy method but does not know how to put the header to the data paste.
    2. To group the data for pre and post test, for pretest it using 12 product that got different namebut for the post test , they are using the same product tested from pre but 1 product can be run for several post test but I want the latest result only of the post test and need to count how muct the post test is carried out.
    3. The data need to be sort using their last three digit from their name

    So far my output is
    Click image for larger version

Name:	My Output.PNG
Views:	11
Size:	127.0 KB
ID:	1224262

    The output should look like


    p/s: I need to do this by automation using the userform input and click button. Let me know if this is impossible

    I have include all the sample related and it have some dummies data.
    DummyData.zip

  • #2
    Anybody?

    Comment


    • #3
      It should be quite simple.

      1. Workbooks.Open("C:\Users\mazman\Desktop\Hilmi\Target Workbook1.xlsx"). This is hardcoded so will only work on your computer, is that what you intended?

      2. The data would be easier to work with if it was formatted as an Excel Table, can you work with this.

      3. You use a Loop to transfer the data, this is inefficient. I would use AutoFilter.
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4


        royUK Hi and good evening.

        1. The workbook later will be saved at my division share folder and I will change the directory.

        2. Yes it is okay for me, but how to use it as excel table? Is it used ado connection?

        3. Is it possible to work with auto filter to match the criteria input from the userform?

        Sorry I am relatively new in excel vba and I need to gain a lot more knowledge on vba.

        Comment

        Working...
        X