Thanks for the feedback Tom. After I add "Alldata", I flip
back to the other sheet with

idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate

which probably isn't the most efficient or prettiest way.

Jason

-----Original Message-----
It shouldn't work as written

One you add the sheet alldata, it is the active sheet.
(and I don't see
anywhere that you change that)

but you set your range to copy with

Set myrng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))

the unqualified Range and Cells refers to the activesheet.

you should have

Set myrng = ws.Range(ws.Cells(1, colndx), _
ws.Cells(ilastrow, colndx))

--
Regards,
Tom Ogilvy


"Jason Morin" wrote in message
...
I'm not a programmer, so I would appreciate any feedback
on this short macro I created. It takes multiple columns
of variable lengths and piles then in column A on a new
sheet. No error-trapping for more than 65,536 rows or an
empty column. Thanks. Jason

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''

Dim ilastcol As Long
Dim ilastrow As Long
Dim jlastrow As Long
Dim colndx As Long
Dim ws As Worksheet
Dim myrng As Range
Dim idx As Integer

Set ws = ActiveWorkbook.activesheet
ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column

With Sheets.Add
.Name = "Alldata"
End With

idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate

For colndx = 1 To ilastcol

ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
..End(xlUp).Row

Set myrng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myrng
.Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
End With
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

End Sub



.