I need help in getting a VBA Codes for my report. I run a report daily, I have around 2000 items, this is an invoice report on all our customers in one worksheet. Each customers has 3 or more invoices, seldom has one invoice only. We have around 300 customers.
Columns as follows:
A - CUSTOMER NAME
B - INVOICE NUMBER
C - INVOICE DATE
D - INVOICE DUE DATE
E - AGE OF THE INVOICE
F - INVOICE AMOUNT
G - FREQUENCY (NUMBER OF DAYS FROM THE FIRST ORDER TO THE NEXT ORDER DATE) - usually those customers with only 1 invoice is blank, meaning no frequency.
This is what I need to do, and we had this project on getting an automated report:
1. I need to get the last invoice date of each customer.
2. Need the total number of invoice/s of each customer
3. The total invoice amount of each customer, meaning from first to the last invoice they have with us.
4. Total Frequency of each customer
5. Average Order date, that is #4 divided by #3
6. Estimated Next order date, that is the answer to #5 plus #1 - the date of the last invoice
7. Next Order Date Due in 7 days, that is the answer to #6 plus 7 days
8. Next Order Date Due in 2 weeks, that is the answer to #6 plus 2 weeks
9. Next Order Date Due in 1 month, , that is the answer to #6 plus 30 days
And there is another sheet which would provide a reminder on which customer to contact on those date every time the report is run.
I have been working on VBA Code but it seems not successful, I have been learning Macros for almost a month now and I am really in need with help on this. I know this is a tough work.
Any suggestions?
Thanks!
Celine
Bookmarks