VBA code to vlookup data from another worksheet

  • I would like to limit the range in selection which only select within column (5) but I don’t know how to fix the code. My purpose is the macro only run if user choose range within column(5) because if they choose the range in other columns, the macro cant run well, otherwise the macro will be stopped or show the message to user that “pls choose in range column(5) only”. Pls help me to solve this problem. Thanks in advance.

  • You may not need the formula at all and let the macro do the work. It would be easier to help and test possible solutions if you could attach a copy of your file, de-sensitized if necessary. Explain in detail what you are trying to do using a few examples from your data and referring to specific cells, rows, columns and sheets.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,
    [FONT="Times New Roman"]I try to update the information (col L,M,N) from Data wb into SHIP VIET wb (col K,L,M) base on invoice (col E). Because I want the macro only run in some lines what I choose in col E so that I use “For Each cell In Selection” and the macro can run well. However, when user choose the wrong col (other cols instead of col E), this code will remove the data in other cols because it will add the formula in col 6,7,8 count from col E. So that I want to reduce this problem by limited the col which was choosed by user, the macro only run if they choose right col E, otherwise the macro wont be run. Thanks your support. P/S: Pls see the attached file.[/FONT]

  • Hopefully, I have understood correctly. Make sure that both workbooks are open. Place the macro below in a standard module in the "Ship Viet" workbook. Change the workbook name (in red) and the sheet name (in blue) to suit your needs. Save the workbook as a macro-enabled file. Run the macro.


    Please note that the macro eliminates the need for any formulas.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps, Thanks for your generous help. The macro run very well, but could you please explain to me which lines of the code to get the data from (col L,M,N) of Data Wb and paste into (col K,L,M) of SHIPVIET? And how can it paste exactly to col K,L,M in SHIPVIET Wb? And if the col in SHIPVIET Wb and Data Wb arenot continously, could you give me some solutions ? I’m very new to VBA, hope that you can help me in this matter. Thanks again,Mumps.

  • This line of code copies the data from

    col L,M,N of Data Wb to col K,L,M of SHIPVIET:



    Code
    1. [align=left][COLOR=#252C2F][FONT=Courier][SIZE=12px]invoice.Offset(0, 6).Resize(1, 3).Value = fnd.Offset(0, 6).Resize(1, 3).Value[/SIZE][/FONT][/COLOR][/align]


    Could you please attach a revised file where "the col in SHIPVIET Wb and Data Wb are not continuous"? This will allow me to see how your data is organized.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    I use macro to add fomular into col T,U,V,X,Y,AC and AD, but the data of sheet Fr orders is too much, so that my code didn’t run well. I vlookup data from Data Wb (Outstanding sheet) into FR orders, and compare have any duplicate with sheet Fabship and Trimship. Pls see the attached file. Thanks for your generous help.

  • Place the macro below in a standard module in the "FR" workbook. You will not need any formulas.


    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    The macro can run exactly, however because of the huge data in my chart, the macro took more than 5min to finish its task :( (full information from col A to col T and nearly 15000 rows). Anyway, thanks for your help so much, Mumps

  • Try this, it will be much faster with your full dataset

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi, I want to ask you one more thing about this code. If I have many Data sheets to fill in, how can I add all charts into this code. Thank you in advance.

    Code
    1. Set srcWS = Workbooks("Data.xlsx").Sheets("Sheet1")


    Ex: DTC – SUMMARY CHART => get data from DTC – UPDATE
    PFE– SUMMARY CHART => get data from PFE – UPDATE
    CTT – SUMMARY CHART => get data from CTT – UPDATE
    …. More than 10 charts like this.