Passing Data from "Form" to Spreadsheet

[Neon520]

Hi Everyone,

What is the best way to pass data from a "Form-like" sheet to Spreadsheet
(columns and rows)?

I created a fillable form in Excel by locking area that user are not
supposed to access (mainly field labels, name, address, and so on), and there
is a little calculation involve in this form. How do I pass the data that
user already input in to a spreadsheet?

My original idea was creating a Macro for Print then Clear the form for
later use. But with 2nd consideration, it doesn't make any sense to have the
user fill this in, print it and then, clear it out, without passing the data
to a spreadsheet for compiling and record-keeping purpose.

Does anyone know what is the best possible way to accomplish this?

Thank you very much,

Neon520

 

[FSt1]

hi
not sure of your layout on your "form" but something like this might work
for you.
assuming that the first cell to transfer on your from is A2 and you want it
transfer to sheet2 cell a2.......

Sheets("sheet2").Range("A65000").End(xlUp).Offset(1, 0).Value = _
Sheets("sheet1").Range("A2").Value

you would need a line like above for each cell you want to transfer. adjust
the ranges to suit.
then to clear the "form".......

range("A2,c2,e2,a3,c3, e3").clearcontents

you would need to add each cell on the "form" that you want to clear.

you would also need a way to trigger the code to run. perhaps a button on
the sheet.

good luck
regards
FSt1

 

[Neon520]

Hi FSt1,

Thank you for your rapid response.

I didn't get to try your code yet, but I have a follow up question for you.

What do I need to do if I want the records to be pending every subsequent
times the "button" is pressed, so that the all the records will be
accumulated every time, rather over-write the old ones.

Hope I explain myself this time.

Thanks again,
Neon520

 

[FSt1]

hi
the code i wrote does not overwrite. it appends to the next availabel blank
row.
Breakdown....
sheets("sheet2"). _ 'working sheet 2
range("A65000"). _ 'start at the bottom of the A column
end(xlup). _ 'go up till you hit data
offset(1,0). _ ' drop down 1 row ie find next blank row
value ' this is the stuff you transfering

regards
FSt1

 

[Neon520]

Hi FSt1,

Thank you for your detailed explanation.
One more quick question if you don't mind.
Part of the data that I pass over also contain Credit Card Information. And
I don't want to transfer the CC # since it is a privacy concern for
everybody. I only want to transfer the last 4 digit of the CC# and put #'s
(12 of them) in front of the 4 digits. Do you know how to manipulate this?

Thank you very much,
Neon520