Excel 2000 - Create a small system to help with inventory

Our company is small and has limited resources.

Our office has 2 computers, each running Windows 7 Pro with Office 2000.

We sell small specialized bottles.  The tops come from a special vendor.  They are somewhat antiquated with regard to computer communication.  We have to adapt to how they communicate.

The prices of the tops can change frequently.  We put our inventory of tops into a spreadsheet because the vendor has a dde link which will only work with Excel.  Using this link we can see price changes immediately.

Currently, the staff creates a new spreadsheet each time there is an order of bottles.  Consequently there are hundreds of small spreadsheets.  

I want to create one spreadsheet and use the tabs in Excel 2000 spreadsheet to input orders.  This way there can be one spreadsheet with many tabs. The first tab will contain summary information.  

I am reasonably familiar with VB.   I am not an expert .

The data entered into each detail tab (not the summary one) is simple:  date, order number, PO number, Purchase qty, Purchase price.  There is a column for each of these columns.  The last column has the DDE link info in it.

My questions:
 -  Both the purchase quantity and purchase price are summed.  The sum goes into the cell at the bottom of the columns.  Let's say the purchase qty sum is in cell F18.  I want to write this sum into the summary tab.  Let's say the  detail tab is called Tab B   .  The summary tab is called TabSum.  What instruction do I use to write the summary to cell C6 in TabSum tab?

-      When a new order is entered, a new tab is used.  Let's say the new tab is called Tab C.  I want the sums from Tab C to be written to a cell just beneath cell C6 in TabSum.  I don't want to  have to manually look at the TabSum to find out what cell is empty and ready for a sum.  I assume  I would create a tab called something like Misc . I guess in there I could indicate the last cell to which data was written.   When sums are to be written to TabSum, then I would read the data in tab Misc, add 1 to the cell number and then I know which cell to write to.  Or is there some way to know which cells are empty in column B in TabSum?  May be you know of a better method?

-      When a new tab is created, eventually sums have to be written to TabSum.  Let's say I am creating a new order in Tab D.  How can I create a button somewhere in Tab D so the user can click on it and the sums will write to a cell in TabSum? May be you might be willing to share a snippet of code which does this ?

-      As data collects in the Sums column in TabSum, I want to be able to graph the data so people can see how much was ordered each month.  Above I mentioned the sums are written in the C column.  Let's assume there is a date for each sum in the B column.  I want to graph the data  summarized by month.  I know there is a function in Excel to parse the month from the date.  Do you first sum all the data in TabSum  by month , write this summed data to new cells in TabSum and use these columns for graphing?   Perhaps you know of a web link  or book which can explain how graphing is done  in Excel 2000.  

-      Finally, when an order is complete, I'd like to write  a routine which would take the data in say Tab C and write  it to another spreadsheet where the data will be archived.  I assume some kind of until - Loop construct can be created to read through all the cells in the tab and write them to an archive.  A button needs to be put somewhere so the user can click on it to start the archive process. Perhaps you may be willing to share a snippet of code which shows how this is done?

-      I assume you would use ADO commands to perform the tasks listed above.  Perhaps you could share ADO commands showing me how you open spreadsheets, write data to cells, call functions, etc.


You may have better ideas on how to handle the tasks above.  Thank you for your help, I'm sure I will have many more questions.