Appending data from multiple columns into a single column.

  • Thread starter Thread starter rband
  • Start date Start date
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
 
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?
 
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.
 
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
 
Back
Top