Appending data from multiple columns into a single column.

R

rband

Hi,

Need help with the following problem.

In a workbook with multiple sheets,I need to take all the data
in a particular column in each worksheet (e.g. all the data in column B in
all worksheets) and append them into a single column.

Can somebody tell me how to create a macro to do this ?

Thanks.
Robin
 
G

Guest

What exactly are you trying to append? Are you trying to
add numbers together or append text (and numbers), eg name
and occupation? Can you give an example?
 
R

rband

Lets say I have 3 sheets in the workbook.

column A in sheet 1: CAT
BAT
HAT
column A in sheet 2: APPLE
2
RAT
Column A in sheet 3: PEAR

I want a macro that would take all data in column A's in all sheets in
the
workbook (or preferably only selected sheets) and combine all this
data into one column in a new sheet.
So column A in Sheet 4: CAT
BAT
HAT
APPLE
2
RAT
PEAR

Thanks.
 
A

Alan Beban

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook (watch for
word wrap in this message):

Sub testIt1()
Dim arr As Variant
Dim i As Long, j As Long, k As Long, m As Long
i = Application.CountA(Sheets(1).Range("A:A"))
j = Application.CountA(Sheets(2).Range("A:A"))
k = Application.CountA(Sheets(3).Range("A:A"))
m = i + j + k
arr = ArrayTranspose(MakeArray(Sheets(1).Range("A1:A" & i),
Sheets(2).Range("A1:A" & j), Sheets(3).Range("A1:A" & k), 1))
Worksheets.Add
ActiveSheet.Range("a1:a" & m).Value = arr
End Sub

Alan Beban
 
Top