Concatenate Sheet into 1 cell?

Hello All,

Just need help either creating a code or a formula that I am unaware of that will allow you to concatenate information from two columns (with several rows) into 1 cell.

Attached is a mock workup of an excel with 3 sheets (Main, AAFB, AAB). The "real one" I am using had private data, but the one I have attached is a smaller version with names changed and data changed.

If you look at the "Main" sheet, You will see 2 cells. What I am trying to do is get a code that is a command or something that I can do to get the info in the many columns of AAFB into cell A2 with carriage returns CHAR(10) between rows and the same for the one below it AAB.

Hope someone knows how to do this?

(Also, I need the command to only concatenate cells in the different sheets (ie AAFB & AAB) dynamically. So if the number of values changes, the concatenate feature will only copy over the rows/columns that aren't blank if it is shorter and will grab the new additions if it is longer.)

Why would you want to do this?

However, try this UDF
In a standard VBa module

Please Login or Register  to view this content.

Then in A2

Please Login or Register  to view this content.

And in A4

Please Login or Register  to view this content.

Remember to wrap the text in A2 & A4

Hope this might help.