Copy Excel data from columns to rows with VBA -

i have little experience vba, , appreciate issue. in basic sense, need convert 2 columns of data in sheet 1 rows of data in sheet 2.

it looks in excel:

and need this:

i've written code transfer headings on sheet 2, , works fine. i'm having issues transferring actual values in correct format. right now, body of code

ws.range("b3").copy ws2.range("c2").pastespecial xlpastevalues  ws.range("b4").copy ws2.range("d2").pastespecial xlpastevalues  ws.range("b5").copy ws2.range("e2").pastespecial xlpastevalues  ws.range("b6").copy ws2.range("f2").pastespecial xlpastevalues 

continued on , on. however, won't work, actual document i'm working on has tens of thousands of data points. know there's way automate process, i've tried has either done nothing or given error 1004.

any appreciated!!

edit: there hundreds of little sections of data, each 18 rows long (1 row frame #, 1 row time, , 1 row each of 16 channels). i'm trying loop step size of 18. possible? i'm fine loops, i've never done loop copying , pasting cell values

this method leverages loops , arrays transfer data. isn't dynamic method gets job done. loops use existing constants, if data set changes can adjust constants , should run fine. make sure adjust worksheet names match names using in excel document. in effect, doing loading data array , transposing onto worksheet.

if data set sizes change quite bit, want include logic adjust loop variables , array size declarations. if case, let me know , i'll figure out how , post edit.

sub movetimedata()  set source = thisworkbook.sheets("rawdata") set dest = thisworkbook.sheets("transposesheet")  const datasetsize = 15  const row15start = 3 const row15end = 18 const row30start = 21 const row30end = 36  const colstart = 2  const destcolstart = 2 const dest15rowstart = 2 const dest30rowstart = 3  dim time15array() integer dim time30array() integer redim time15array(0 datasetsize) redim time30array(0 datasetsize)  dim x integer dim y integer dim c integer c = 0  x = row15start row15end     time15array(c) = source.cells(x, colstart).value     c = c + 1 next x  c = 0 x = row30start row30end     time30array(c) = source.cells(x, colstart).value     c = c + 1 next x  x = 0 datasetsize     dest.cells(dest15rowstart, x + destcolstart).value = time15array(x) next x  x = 0 datasetsize     dest.cells(dest30rowstart, x + destcolstart).value = time30array(x) next x  end sub 

edit-> think looking after reading edits

sub movetimedata()  set source = thisworkbook.sheets("rawdata") set dest = thisworkbook.sheets("transposesheet")  const numberdatagroups = 4 const datasetsize = 15 const stepsize = 18  const sourcerowstart = 3  const sourcecolstart = 2  const destcolstart = 2 const destrowstart = 2    dim x integer dim y integer dim currentrow integer currentrow = destrowstart    x = 0 numberdatagroups     y = 0 datasetsize         dest.cells(currentrow, y + destcolstart).value = source.cells((x * stepsize) + (y    + sourcerowstart), sourcecolstart)     next y     currentrow = currentrow + 1 next x   end sub 

now key working knowing how many groups of data dealing after data dump. either need include logic detecting or adjust constant called numberdatagroups reflect how many groups have. note: leveraged similar technique traversing arrays have data stored in row major format.