from multiple to one column(s)

[T De Villiers]

I have two worksheets: ‘Data’ and ‘Dump’,

In ‘Data’ I have roughly 1,000 rows (can vary) and 20 cols (can vary) of data in a sheet
Cols C-X in this case but this can vary with the positioning of the column headers.
So I’ve already defined the number of rows as variable noofrows and cols as acol,bcol,ccol,dcol……etc.

I need to transfer from ‘Data’ to ‘Dump’:

So if in ‘Data’ column acol has 27 rows these will be transferred to Range A1:A27 in ‘Dump’
So if in ‘Data’ column bcol has 27 rows these will transferred to Range A28:A54 in ‘Dump’
Etc.


Many thanks in advance for your help

[rylo]

HI

Try this

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Dump")
  OutSH.Range("A:A").ClearContents
  Sheets("Data").Activate
  For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp)).Copy Destination:=OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  Next i
End Sub

rylo