Saving data from an order into a excel repository?

[drucey]

Excel people, my heroes...

I have a fantastic purchase order form i've been working on for a week
now, i love it!

It looks just like the old paper version
It fits exactly on an A4 page
It emails great
It faxs great
It calls up all existing placed orders
All partially recieved orders, all completly recieved orders
All draft orders


But when i save an order to do any of the above, it saves the main
sheet, renames it to OrderNumber###.xls, removes some sheets and
functionality that should only be present in the main purchase order
sheet, and works fine.

But it's still over 100k per saved order.
Although they're then saved, and easy to open again and reprint/fax
etc..

I'm thinking it's not the most efficient way of saving them, especially
if we have a few thousand orders per year!

So, i'm after any ideas to help me get my head around this.
Is it possible for a macro to take certain cells and save the value
into another workbook?
And then of course, be able to repopulate the main template using data
from this other workbook?
That way, every order needed to be saved would would only require a row
in a workbook instead of 100k



Possible? Much work?

 

[idyllicabyss]

It will depend somewhat on whether you have used contrrols directly on
a sheet or if you have created a UserForm but essentially you just need
to get coding!
If all your objects are the same you may be able to loop through them
rather than hard code. eg textbox1, textbox2, textbox3 etc
If you have a save button or something, the code would be along the
lines of

src = Sheets("Form")
dest = sheets("Database")
NR = ' use code to determine the next row - plenty of examples on this
website
dest.cells(NR,1).value = TextBox1.value
dest.cells(NR,2).value = TextBox2.value
....etc

or this should work if they're all text boxxes and you laid them out in
order they are to be stored in;
for i = 1 to src.Shapes.count
dest.cells(NR,i).value = TextBox(i).value
next i

I haven't tested this but I used something similar a while ago for a
picking list.