Merge first column From Multiple Sheets Into One Sheet

see attachment, run macro "test"

its workin on the raw data that I supplied, thanx, but it not working on the actual data, getting the error message "Run time error 1004" No cells were found.

it looks for text and data in column 1 as in example. So the example do not correspond to the real data in terms of data type

Hi, thanx mate, this is producing the desired result with the actual data.

Cheers, if u can explain the code, it wud be gr8 for me.

Originally Posted by snb

For Each sh In Sheets
--
Next
'Walk' through all sheets in the workbook
If sh.Name <> "Result" Then
If the name of the sheet isn't 'Result' then do:
sh.UsedRange.Columns(1)
determine the area that has been used (values, formulae, etc) in the sheet; take column 1 of that area.
Application.Transpose(..)
convert that first column into a 1-dimensional array
join(...,"|")
convert that 1-dimensional array to a textstring with a pipeline "|" as separator
c01 = c01 & "|" &
add this textstring to the variable c01 in which all previous textstrings have been stored.

Sheets("Result").Cells(1).Resize(UBound(Split(c01, "|")))
Select a range in sheet 'Result' that has the same size as the number of separate values in textstring c01
split(c01,"|")
Convert the textstring c01 into a 1-dimensional array
application.transpose(..)convert the 1-dimensional array to a vertical one.

And I prefer
If you can explain the code, it would be great for me
to
if u can explain the code, it wud be gr8 for me.

Hi,

Thanks for the detailed explanation. Noted, will avoid using slangs in conversation. Can you suggest what all changes I should make if the data that I'm tryin to grab/consolidate is on a seperate excel file "RawData.xls". This works well but it also fetches the data from the sheets in the workbook that are not part of this exercise.

Also, seen your message that you would like to review my curriculum. Can you messgae me your email address so that I can forward it.