excel - Copy data between worksheets -

the problem have on hand is: have excel file multiple worksheets , should populate cells in worksheet b values cells in worksheet a. made little macro doesn't work should. either go in totally different cells, throws , error or copies formula , not value.

the specifics are:

data in worksheet in complete column h , starts in second row. data in these cells concatenate different fields.

the loop should take first value h2 in worksheet , put in worksheet b, b1. move onto h3 in worksheet , put value in worksheet b, c1 , on.

it should till there no value left in worksheet column h.

     sub test2()  ' select worksheet  worksheets("a").activate  ' select first cell data  range("h2").select  ' loop until no data present    until isempty(activecell)  ' helper variable startpoint in worksheet b  dim integer = 8       ' copy first value      selection.copy       ' go different worksheet      worksheets("b").activate       ' select first data entry point      activecell(1, i).select       ' paste data      selection.paste       ' move helper variable in next column      = + 1       ' return worksheet      worksheets("a").activate       ' select next row      activecell.offset(1, 0).select    loop     end sub 

if use static code works defined range

sub macro1() worksheets("a").activate range("h2:h12").select selection.copy worksheets("b").activate range("b1:h13").select selection.pastespecial paste:=xlpasteall, operation:=xlnone, skipblanks:= _     false, transpose:=true application.cutcopymode = false end sub 

seems more elegant don't know how make "flexible" doing till comes empty cell.

the next problem ran in worksheet b should matrix stuff. after values in row 1, column b , on have formula:

=table2[[#headers];[column1]] 

now formula should extended numbers of added data.

so like

=table2[[#headers];[column2]] =table2[[#headers];[column3]] .... 

and on depending on numbers of added values.