Can VBA be used to automate my weekly sales report?

[Mangosteenlu]

Hi All,

Good afternoon.

Can anyone help me with the automation of the task I perform every week which is bit tedious? Thank you very much in advance.

I have 4 columns: -

Column A: the lookup values based on Column B (I have the lookup list on a separate tab)
Column B: Customer’s code ( sometimes two of three customers’ code need to be added the values together as they are under the same category. Hence I created the Column A as my helper column to show them as the same categories)
Column C: Gross amounts for the each customer for previous week

I download the daily report from our ESP (Sales) system, which contains each customer’s daily revenue for last week on every Monday morning. I drag my formula in Column A to make sure each customer has the appropriate category. Then I need to re-arrange my data based on the order of Column A first, column B second. Also I need to re-format the Column C as a number with two digits.

For each category, I need to find the last cell in column C, adding them together showing the total number in Column D. The total number has to be on the same row of the last cell for that category. For example,


Column A Column B. Column C. Column D
Club sales. ABC. 12,320.25
Club Sales. FGR. 9,500.00
Club Sales DEF. 6,500.78. 28,321.03
Shop Sales. SAT. 10,250.00
Shop Sales. OPT 32,000.00. 42,250.00

After done that, I need to copy Column B, C and D to a new file in a CSV format. As our accounting system only accepts the exact format like I described above.

Is it doable by using VBA coding?

Kind Regards
Manosteen

 

[dermie_72]

Hi Manosteen, Yes this can be done via VBA. Anything you would do manually can be replicated in VBA. My suggestion would be to record a macro of the actions you would do with the report, then review the code produced to encompass the range as a variable. I think this was explained how to do some of these tasks in the books suggested to you in the 10th Nov 2023 post.
You can then rerun the code to perform the same functions each time. This will include any formulas and sorting. If you are manually entering data though, you will need to consider the logic of where you are getting the information and why you are manually entering the data, then translating it to VBA to replicate your logic.