Concatenate and Copy Paste as values for whole sheet

[padawanofvba]

Dear all,

first of all I am new to the forum (and to VBA programming, but I had found answers to many excel-related questions here over the past year. So I thought I'd also give it a go.

I just started looking at VBA tutorials and want to understand more about it.

One question I have: I work with large data bases, and need to do Vlookups regularly. As the fields are sometimes numbers, sometimes text / mixed etc., they do not always work immediately. To circumvent this formatting problem, so far I have been using =Concatenate() in a separate column, copied it all the way down, then copied & pasted these fields as values, and then replaced the previous column with the "Concatenated" values.

My question is: is there a macro one could use to

1. =concatenate all cells in the workbook
2. copy / repaste them in the same spot as values?

This would save an immense amount of time for me, and I would also learn the VBA logic behind it.

Thanks in advance and best,

A Padawan

[JBeaucaire]

Sure. Outline your exact steps more fully and we can make a macro that does the exact same thing.

Or, turn on the macro recorder and let it record you going through your current manual process from start to finish. Then turn off the macro recorder and paste the recorded code here, we can adapt that to make it reusable and dynamic.

[padawanofvba]

testtestestest

[JBeaucaire]

Why the test? See post #2 in case you missed it.