Need VBA code to get the data from another excel and use few formula, write the output back to excel

Occasional Contributor

Hi All,


I have a monthly data in a  excel workbook named as workbook1, need a VBA code, put it into a button and placed in workbook 2. And by using the code to get the data of one column (named as A) from the  Workbook 1 and use some formula , then write the output back to the workbook1 into a column (named as B)


Formula that was using in this scenario : =TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR","CASL Block","Virtual phone ","unsatisified"},A1)),{"Shared Mailbox","DL ","NSSR Request","NSSR Request","O365 block List","Vioce Mail ","Unsatisfied Requests"},""))


I have tried but it not accomplish the goals. So Anyone could help on this issue.


@Hans Vogelaar, If possible could you look into this scenario.


Thank you.


6 Replies

@Hans Vogelaar , Please find the sample workbook 1 in the attachment and the 2nd workbook which is an empty workbook. In that need to write a VBA, then put it into button. The function is whenever we click the button in the excel workbook 2 , it will get the column A data from Workbook 1 and process the  formula that you have shared previously, then write the data back output to Workbook 1 in Column B. 


Thanks for your help on this.


See workbook2 (now a macro-enabled workbook)

@Hans Vogelaar  Thank you so much for your Prompt help on this scenario.


After  I clicked the button its working but the same output is pasted in the workbook2 as well like the below screenshot.

Is there any way to get rid of them.




best response confirmed by allyreckerman (Microsoft)


I did what you asked, but I already suspected that it wasn't what you wanted.

Remove the lines

        ' Optional
        ws2.Range("A2:A" & lr).Value = .Value

from the code.