Multiple Columns to Single Columns
...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
|