V basic sorry - order form that extracts/copies data to simple table on different worksheet

[Pots21]

This is terribly basic so apologies in advance but I've spent three hours researching on the web and my lack of the correct terminology is proving a real hindrance to working out a solution.

I'm trying to help modernise a lovely old family microbusiness that currently uses a triplicate hand written ordering system, combined with hand written address labels, dispatch notes and order processing. I'm working with excel 2003 and there's no cash at the moment for anything else.

i want to create a really simple order form in excel that includes address label etc, which I can do relatively easily. But I also want to be able to automatically copy/ export the information to a simple grid to pull all the orders together, possibly split up by key categories, on different worksheets or a single one if need be. Long term I'd like to design it so I can upgrade and expand it as time goes on to some thing more complex that ties in with stock.

At the moment I'd like to be able to copy/export from the order form template:

date, customer name, contact details, one of four categories of products, colour of product and a notes section.

3 sept 2013. Joe blogs tel email addresss 1 shirt Blue daisies Notes - please call customer before dispatch to ensure they're at home for delivery.

In a dream world I'd like each of the four product categories to copy the information to a different table/ worksheet. So shoes go the shoe excel table as a list I can print for the shoe workshop ditto shirts trousers jackets for eg. It's a small firm so it's unlikely any table would have more than say 60 orders on - for now at least.

I'm sure this kind of query has been answered a million times already but I've spent ages looking for terms like extract and export data and everything I find is way too complicated or too simple! I'm clearly using the wrong phrases let alone getting to work on my new system! Any help at all would be massively appreciated.

thanking you a lot in advance.

 

[Pots21]

A quick add - as table data is moving to needs to be dynamic am I researching macros or pivot tables or ... Or is this just too basic. Just need right subject headings to research if nothing else for library on this site...

 

[orangefury90]

Hello,

Hopefully I will be able to help you out. It sounds to me like you need to do the following:

1. Make a Module by pressing alt+F11 in Excel. Then go to insert module. You will see something like "Module 1" on the left hand side.

2. Click on Module 1 (above) and begin writing your code. The basic format in which you can copy/paste what you want is below:

Sub copy_info_program()

dim yourvariable as string

yourvariable = Thisworkbook.Sheets("YOUR TEMPLATE WORKSHEET").Range("C4")

ThisWorkbook.Sheets("ANY OTHER WORKSHEET").range("E3") = yourvariable

end sub

What this program does is copies from one sheet and you can then paste the value into another sheet. To copy large amounts of data is not too much more complicated (using for loops and while loops) however, it looks like you need to play around and learn how to make a basic program first!

Hope this helps.

 

[Pots21]

Thank you SO much. I'll start having a play and come back when I've got a bit further. I'm fantastically grateful, you've gone to so much trouble. Thank you orangefury.

 

[orangefury90]

No problem! glad to help