Excel VBA looping: reshape column into table -

i have not used vba sometime rusty... have number of records stored vertically (in single column) , want use vba stack them side side (into table).

my general thoughts how flow:

1. start @ first range
2. copy data
3. paste data in cell b3 of output page (just named sheet2)
4. loop previous range , offset 51 rows
5. copy data
6. paste data in cell c3 of output page (offset 1 column each time)

my attempt far:

    sub macro1()                 fiftyone = 51 ' offset 51 rows every chunk         startrange = "l262:l303" ' start @ range of data copy, each chunk identical in size         outputrange = b3 ' paste in output @ b3, need offset 1 column each time               range(startrange).offset(fiftyone, 0).select         selection.copy          sheets("sheet2").select         range("b3").offset(0, 1).select         activesheet.paste         end sub 

i know rather lame attempt tackle flow, struggling how loop through this. appreciate advice on how this, or better approach general flow.

---

edit after accepting wolfie's answer:

i want assign column headings, getting values c258 , looping down (in similar way before) 51 rows @ time, paste row 2 of sheet2 (b2, c2, ...).

here current attempt:

sub nametotable()     ' assign first block range, using changable parameters     ' remember "dim" of variables, using colon line continuation      dim blocksize long: blocksize = 51     dim firstrow long:  firstrow = 258     dim rng range      set rng = thisworkbook.sheets("sheet1").range("c" & firstrow & blocksize - 1)     ' tablestart upper left corner of "pasted" table        dim tablestart range: set tablestart = thisworkbook.sheets("sheet2").range("b2")     dim long                      ' looping variable     dim nblocks long: nblocks = 10  ' we're going loop nblocks number of times      = 0 nblocks - 1         ' actual value copying, using resize set number of rows         ' , using offset move down original values , along "pasted" columns         tablestart.offset(0, i).resize(blocksize, 1).value = _                                                       rng.offset(blocksize * i, 0).value     next end sub 

your logic seems alright, code create 51 x n table, lining each vertical block of 51 cells in own column.

note, it's much quicker assign .value copying , pasting, if need formats copy/paste or set format properties equal.

sub columntotable()     ' assign first block range, using changable parameters     ' remember "dim" of variables, using colon line continuation     dim blocksize long: blocksize = 51     dim firstrow long:  firstrow = 262     dim rng range     set rng = thisworkbook.sheets("sheet1").range("l" & firstrow & ":l" & firstrow + blocksize - 1)     ' tablestart upper left corner of "pasted" table     dim tablestart range: set tablestart = thisworkbook.sheets("sheet2").range("b3")     dim long                      ' looping variable     dim nblocks long: nblocks = 10  ' we're going loop nblocks number of times     = 0 nblocks - 1         ' actual value copying, using resize set number of rows         ' , using offset move down original values , along "pasted" columns         tablestart.offset(0, i).resize(blocksize, 1).value = _                                                       rng.offset(blocksize * i, 0).value     next end sub 

set nblocks value suit needs, number of resulting columns in output table. dynamically knowing number of rows in original column. or use while logic, careful make sure exit of course!

dim long: = 0 while rng.offset(blocksize*i, 0).cells(1).value <> ""     tablestart.offset(0, i).resize(blocksize, 1).value = rng.offset(blocksize * i, 0).value     = + 1 loop 

---

edit: column headings, keep in mind column headings 1 cell, so:

' change this: set rng = thisworkbook.sheets("sheet1").range("c" & firstrow & blocksize - 1) ' this: set rng = thisworkbook.sheets("sheet1").range("c" & firstrow) 

tip: + used adding numerical values, whilst & used concatenating stings.

now when you're looping, don't need resize, because assigning 1 cell's value 1 other cell. resulting sub:

sub nametotable()     dim blocksize long: blocksize = 51     dim firstrow long:  firstrow = 258     dim rng range         set rng = thisworkbook.sheets("sheet1").range("c" & firstrow)        dim tablestart range: set tablestart = thisworkbook.sheets("sheet2").range("b2")     dim long: = 0     while rng.offset(blocksize*i, 0).value <> ""         tablestart.offset(0, i).value = rng.offset(blocksize * i, 0).value         = + 1     loop end sub