Copy Data from current sheet with Auto Filter/Looping then paste to another sheet

I am a starter of Excel VBA (with no programming experience / background), trying to learn to use auto filter and looping to copy data from one sheet to another sheet.

1) Please help out on error handling issue when auto filter returns no data.
- Kindly try to modify the code in Module 1 with step by step comments. (if possible)
- the existing codes only works when auto filter returns sorted data. In the attached sample, there is no HKD invoice records and the result is a mess.
- In the desired result sheet, I also want to know how to achieve the result in the "GRAND TOTAL" row.

2) I also want to know more about the looping logic with this example.
- I only know the very simple logic with one variable at the moment. I think I need to add another variable but I don't know how to work with the logic.
- Please help to modify the code in Module 2 with step by step comments. (if possible)

Thanks in advance !

Hi,

first operate manually a so easy Excel Basics 2 steps without any bad looping logic :
sort your data on column C then use the Data SubTotal feature …
Once it works as expected, if you really need a VBA code,
restart and just before to operate activate the Macro Recorder
in order to get your own base code, so easy, just try ! …