Not to sure where to post this and if it’s possible. I believe what I am attempting to do is functions running within a macro but with a bit of VBA to execute the functions at particular times within the process (ie: functions will not automatically run all at the same time).
The process I would like to achieve is:
From the source data worksheet,
1. sort by Column A in worksheet 1,
2. Then, [Lookup] parameter ‘x’ in column B of worksheet 1
3. Then, copy all rows with parameter ‘A’ to worksheet 2, row 2,
4. Then, repeat process but [Lookup] parameter ‘y’
5. Then, copy all relevant rows to new worksheet 3,
6. Then, repeat process but [Lookup] parameter ‘z’
7. Then, copy all relevant rows to new worksheet 4
8. Then, sum numerical column in row 1, of worksheet 2(this row will be linked to cell in another workbook,)
9. Then, sum numerical column in row 1, of worksheet 3(this row will be linked to cell in another workbook,)
10. Then, sum numerical column in row 1, of worksheet 4(this row will be linked to cell in another workbook,)
11.
and so…
I think this is the process needed to achieve required, though some steps may not be required, is this possible?


Trader 4 Invoice 2118991 1 PRODUCT 100 1838 CNY CY APAC CN HARDWARE
Trader 4 Invoice 2118959 3 PRODUCT 100 4701 CNY CH APAC CN HARDWARE
Trader 8 Invoice 3200002263 1 PRODUCT 100 406.25 EUR CN EMEA AT SOFTWARE
Trader 8 Invoice 29671 1 PRODUCT 100 25.15 EUR CN EMEA DE HARDWARE
Trader 4 Invoice 2119050 6 PRODUCT 101 367.6 CNY LT APAC CN HARDWARE
Trader 4 Invoice 2118967 4 PRODUCT 101 4701 CNY GB APAC CN HARDWARE
Trader 8 Invoice 3200002292 1 PRODUCT 101 840 USD CN EMEA LT SOFTWARE
Trader 8 Invoice 29711 1 PRODUCT 101 25.15 EUR CN EMEA DE HARDWARE
Trader 4 Invoice 2118905 3 PRODUCT 102 1838 CNY CY APAC CN HARDWARE
Trader 4 Invoice 2118973 10 PRODUCT 102 2350.5 CNY HK APAC CN HARDWARE
Trader 7 Invoice 150120 1 PRODUCT 102 4168.75 EUR CN EMEA CY SOFTWARE
Trader 8 Invoice 29617 1 PRODUCT 102 310.67 EUR CN EMEA DE HARDWARE
Trader 4 Invoice 2119050 4 PRODUCT 103 1838 CNY LT APAC CN HARDWARE
Trader 4 Invoice 2118973 2 PRODUCT 103 2350.5 CNY KZ APAC CN HARDWARE
Trader 7 Invoice 101652 4 PRODUCT 103 0 ILS CN EMEA IL SERVICE
Trader 8 Invoice 29519 1 PRODUCT 103 27.81 EUR CN EMEA CH HARDWARE
Trader 4 Invoice 2119050 10 PRODUCT 104 3676 CNY JP APAC CN HARDWARE
Trader 4 Invoice 2118990 2 PRODUCT 104 28200 CNY IL APAC CN HARDWARE
Trader 7 Invoice 101652 2 PRODUCT 104 3907.5 ILS HK EMEA IL SERVICE
Trader 8 Invoice 29321 2 PRODUCT 104 13.43 EUR FR AMER US HARDWARE
Trader 4 Invoice 2118875 3 PRODUCT 105 919 CNY DE APAC CN HARDWARE

Trader (no) = Division (Column A)
Invoice = Document Type (column B)
Interger = Invoice Number (Column C)
Single Interger = Invioce Line No. (Column D)
Product (no.) = Product (Column E)
Interger = Product Unit Cost (Column F)
Currency Code = Transactional Currency (Column G)
Region = Sold to Region
Country = Ship to Country
Region 2 = Bill to Region
Country 2 = Bill to Country
Hardware etc = Product Family

Not sure how to attach a sample but above is a copy and paste from spreadsheet (though justification may get messed up)

So Sort on Column A [trader]
then lookup all trading countries from [UK] which billed to [EMEA]
then copy result to new worksheet row 2

Now lookup all lines which sold from [CN] which were billed to [Emea]
copy these results to new worksheet from row 2 down

and so....

thus All Data relating to each countries sales which billed and shipped to a particular region are posted to new worksheet, all data from that country which billed to the same region but shipped to a different region post to another worksheet, process repeats until all data has been reallocated to new worksheets by customer, bill to region, ship to region

hope it makes sense..

I think this VBA will work for adding new sheets....

Sub AddAsLastWorksheet()

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"

End Sub

But not sure how to get stripped out copied rows from row 2 not row 1


any help would be fantastic - thanks in advance