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


    • So far I got the copy method but does not know how to put the header to the data paste.
    • 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.
    • The data need to be sort using their last three digit from their name

    So far my output is
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"My Output.PNG","data-attachmentid":1224262}[/ATTACH]

    The output should look like
    [ATTACH=JSON]{"data-align":"none","data-size":"full","data-tempid":"temp_75611_1570676820671_19","title":"Target Output.PNG"}[/ATTACH]

    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.

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

  • [USER="75"]royUK[/USER] 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.