Don't know where to start with programming a button

BillyMo

New Member
Joined
Oct 4, 2006
Messages
2
I am a complete newbie to excel.

Is it possible to create a sheet that exports its data to a different worksheet which is printed and a different workbook for archival purposes?

I'll give the scenario:

I would like to enter a new customer's name, address, phone number, and item they bought into a sheet (B1:B4) and hopefully click a button.

I would then like it to export that data to the next available row (A2:D2, A3:D3, etc) on a different workbook so that I can archive all of my customers (getting it to fill the next row seems to be the biggest challenge for me so far) and also export the same data to sheet 2 of the original workbook.

Sheet 2 is organized as an index card to keep a hard copy in a tickler file and is printed.

After that, I hope it could clear the original cells so I can type the next customer's info. It would be a bonus if it would automatically print the card when I click said button, but that may be asking too much.

If someone could even tell me where to start I'd gladly do the research to formula's etc myself.

Thanks in advance.

Take care.

-Bill
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Not hard at all, especially using a button!

Here's a macro (VBA Code) that will do it:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> yada()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
        
        Sheets("Sheet1").Range("A2:D2").Copy LastRow.Offset(1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Just to try, open the VB (Editor) with Alt+F11, then goto Insert-->Module and paste that code in. ALT+Q to exit back to Excel.

Make some entries in sheet1 A2:D2 and run the macro (or assign it to a button from the Forms toolbar, you'll get the prompt when you're done drawing it), then look at sheet2.

That will build a sequential dataset. The rest is more complicated, but there are lots of folks here to help with that. ;)

Smitty
 

BillyMo

New Member
Joined
Oct 4, 2006
Messages
2
It looks to be working well except that when it exports the data, it exports it into columns instead of rows. How can I fix that?

Let me also say that I can't tell you guys how much I appreciate the help and lack of flaming. Lifehacker was right to recommend you.

Take care.

-Bill
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,451
Members
410,612
Latest member
MrACED
Top