Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim HowManyToCopy As Long
Dim RngToCopy As Range
Dim DestCell As Range

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

HowManyToCopy = 14

With NewWks
Set DestCell = .Range("A1")
End With

With CurWks
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'or if row 1 may not always be used...
LastCol = .Range("AUG1").Column

FirstRow = 1

For iCol = FirstCol To LastCol
Set RngToCopy = .Cells(FirstRow, iCol).Resize(HowManyToCopy)
RngToCopy.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count)
Next iCol
End With
End Sub


Blake wrote:

I have data in different columns starting with column A going down row 1 thru
14. So column B row 1 thru 14, column C 1 thru 14, etc.

I need to consolidate everything from column a1-a14 to agu1-agu14 into one
column, for example:

a1
a2
a3
.
.
.
a14
b1
b2
b3
.
.
.
b14

etc.

How do I go about doing this?

I tried doing transpose and that didn't work.

I'm using Excel 2007 if that makes a difference.

Thanks
Blake

--

Dave Peterson