VBA code to split worksheet by invoice type

pbolali I've attached your sample file. It contains two additional sheets. I also Tabled your data on Sheet1. The sheet 'PivotTables' is a PivotTable from the Table as the data source (it's easier to use Tables than standard ranges in my opinion). Then, I dropped the 'REMARKS' field into the Filter area and set it to the first value. Then, I copied the PivotTable two more times (there are only three PivotTables in this example).

PivotTable caveats:

They do not manually refresh. You have to refresh them yourself if data is added to the Table. I tend to add code to the worksheet housing a PivotTable to automatically refresh their data when the worksheet is activated. Also, and more importantly, this example has three PivotTables stacked vertically - this is generally not a good idea. Instead, having a PivotTable on each sheet would negate this issue. The reason is data will want to grow vertically, and two cannot overlap one another. This is also a general rule for Tables as well.

There is another sheet titled 'Subtotal'. This is a copy of the data, although it is not in a Table, because the subtotal feature doesn't work with them. With the data, on the Data ribbon tab, click the Subtotal button. Ensure each change is set for the field 'REMARKS' and you check the columns you want to sum. I've done this to mimic the example you set in Sheet2. It is a fast and easy way to see subtotals by a specific field. These subtotal values will automatically update when you re-apply this feature.

Regarding the VBA code you posted, if either of the above solutions work for you it would negate the need for it. I'm a very big fan of VBA, but if there is a native solution which will work for you, I'll generally recommend using it instead. With that being said, if you still want a VBA solution, I will code one for you.