button - VBA - Creating a Data Sheet from a User Form -

i'm hoping can me issue. i've searched site correct coding spreadsheets, nothing seems work.

i have 2 spreadsheets: have front facing form (sheet1) user can input information. once user done inputting information, press button transfer data large data spreadsheet ("records"). once data copies sheet1 records, want sheet1 clear (so next user has blank form work from). additionally, when new user inputs information, want information go on line below previous user's data input on records.

i hope makes sense... code i'm using now, doesn't populate in "records" (i've made mess of this).

sub button() dim reftable variant, trans variant reftable = array("c = e6", "d = e7", "e=e8", "f=e9", "g=e10") dim row long row = worksheets("records").usedrange.rows.count + 1 each trans in reftable     dim dest string, field string     dest = trim(left(trans, instr(1, trans, "=") - 1)) & row     field = trim(right(trans, len(trans) - instr(1, trans, "="))) next  end sub 

does have advice coding? please let me know!

try code.

the key bit set fields per order want them appear in worksheets("records") in columns a v.

for example, in example e6 maps column a, e7 column b etc.

sub mapinputtocolumn()     dim fields range, rw integer, col integer      set fields = union(range("e6"), range("e7"), range("e8"), range("e9"), range("e10"))     rw = worksheets("records").usedrange.rows.count + 1      col = 1 fields.count         worksheets("records").cells(rw, col) = fields(col, 1)     next col      fields.clearcontents end sub