...misread your post .....try this:

Sub CopyAndMerge()

Dim inrange As Range, outrange As Range
Dim cValue As Variant
Dim r As Long, c As Integer


Set inrange = Application.InputBox(prompt:="Select Input range", Type:=8)
Set outrange = Application.InputBox(prompt:="Select Output range", Type:=8)


Do While Application.Or(outrange.Rows.Count < 1, outrange.Columns.Count < 1)
MsgBox "Output range must be a single cell"
Set outrange = Application.InputBox(prompt:="Select Output range", Type:=8)
Loop


For r = 1 To inrange.Rows.Count

For c = 1 To inrange.Columns.Count
outrange.Value = inrange(r, c).Value
Set outrange = outrange.Offset(1, 0)
Next c

Next r
End Sub

"Toppers" wrote:

Try:

Sub CopyAndMerge()

Dim inrange As Range, outrange As Range
Dim cValue as variant
Dim r as long, c as integer

Set inrange = Application.InputBox(prompt:="Select Input range", Type:=8)
Set outrange = Application.InputBox(prompt:="Select Output range", Type:=8)

Do While Application.Or(outrange.Rows.Count < 1, outrange.Columns.Count < 1)
MsgBox "Output range must be a single cell"
Set outrange = Application.InputBox(prompt:="Select Output range", Type:=8)
Loop

For r = 1 To inrange.Rows.Count

Cvalue = ""
For c = 1 To inrange.Columns.Count
Cvalue = Cvalue & inrange(r, c).Value
Next c

outrange.Value = Cvalue

Set outrange = outrange.Offset(1, 0)
Next r
End Sub

HTH

" wrote:

Hi Guys,

I have a spreadsheet which has data in a number of columns which I need
to copy to another sheet in one single column.

What I need to be able to do is select all the columns from the source
sheet (this number may vary), and then have select a start point in
another sheet and ahve all the columns sequentially pasted in.

I've started to write a macro to do this but haven't got very far, can
any help me out?

Thanks

Simon