Question

A17BPG on Wed, 27 Sep 2017 18:55:55


Hi I am trying to produce an order system for Butchers using Excel. I have produced a sheet containing 54 Columns across and 1000 Rows. The Column headings are Cust.No,, Customers Name, Customers Tel No. Turkey, Turkey Size, Price Per. Kilo. These headings are repeated across the page for Chicken, Goose, Duck, Game and other products etc etc all with the Size, Price Per Kilo following each one of these headings. I would like to use a Macro to collect  Data from the Rows across Order Sheet ignoring Empty cells. Then place the Data retrieved into a  Sheet called “Invoice”. under the headings as follows:

 

“Cell C1” INVOICE #  : [1234] “Cell D1”[INVOICE # ]

 

“Cell C3” Cust.No. : “Cell D3”[Cust.No]                           

“Cell C4”Customers Name  : “Cell D4”[Customers Name ]

 

“Cell C6” Item Purchased     “Cell F6” Size    “Cell H6” Price Per Kilo/Each “Cell K6” Total

 

                                                                                                            “Cell J30” Grand Total

 

The Total Field would be a Result of SizexPrice Per Kilo, Grand Total result from totalling the Total Column.

 

Within the Macro I would like to generate the Invoice Number and as usual increment by One each time Macro is run, then when each invoice is complete Print It then save it using Customers Name as name of saved file.

CAN ANYONE HELP. If needed and if possible could attach file showing Order Sheet and layout for Invoice.

 



Sponsored



Replies

Chenchen Li on Fri, 29 Sep 2017 05:44:51


Hello,

I suggest you share your file via OneDrive. Please follow Share OneDrive files and folders and share the link here.

For a general suggestion, I suggest you try to refer below link to read Excel cells.

#How to loop through a list of data on a worksheet by using macros in Excel

https://support.microsoft.com/en-sg/help/299036/how-to-loop-through-a-list-of-data-on-a-worksheet-by-using-macros-in-e

Regards,

Celeste


MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


Deepak Saradkumar Panchal on Tue, 10 Oct 2017 08:19:18


Hi A17BPG,

I can see that you want to develop order system using Excel.

in which there are already records are available in the sheet and you want to store it in to another sheet.

then take the data from the cells and generate a bill and want to print it and also want to save it as an individual file with customers name.

you can develop a macro for that but here I think that it will not be the best solution for your above requirement.

instead of using Excel, you can try to use Access.

with the help of Access you can properly develop Order System.

in which you can create tables for your customer data, purchase and sales data, Order Data, etc.

you can also import all the data from Excel to Access.

then you can also generate bills as a report.

in this way you can generate bills for individual order and you not need to save to new file.

all the reports will be saved with in your database.

so in future , when ever you need it you can easily access it.

you can also generate other reports to check the monthly/ yearly orders, sales, profit.

you can fetch the data using queries for completed orders, remaining orders, which orders needs to be completed in current month, how many orders you finished in last month etc.

to enter the new data you can generate forms which are extremely user friendly.

so you can try to check the MS Access and if you think it is best suitable for your requirement then you can try to use it.

it is much sophisticated , user friendly, best suitable for storing data, much secure and have all the features to fulfil your requirement.

if then also you want to use the Excel to develop Order System then try to provide detailed information about it.

we will try to provide you some sample codes, based on that you can try to develop your own code to meet your requirement.

Regards

Deepak


MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.