D
dakco
Not to sure where to post this and if it’s possible. I believe what I a
attempting to do is functions running within a macro but with a bit o
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 b
linked to cell in another workbook,)
9. Then, sum numerical column in row 1, of worksheet 3(this row will b
linked to cell in another workbook,)
10. Then, sum numerical column in row 1, of worksheet 4(this row will b
linked to cell in another workbook,)
11.
and so…
I think this is the process needed to achieve required, though som
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 PRODUC
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 fro
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 shippe
to a particular region are posted to new worksheet, all data from tha
country which billed to the same region but shipped to a differen
region post to another worksheet, process repeats until all data ha
been reallocated to new worksheets by customer, bill to region, ship t
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
attempting to do is functions running within a macro but with a bit o
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 b
linked to cell in another workbook,)
9. Then, sum numerical column in row 1, of worksheet 3(this row will b
linked to cell in another workbook,)
10. Then, sum numerical column in row 1, of worksheet 4(this row will b
linked to cell in another workbook,)
11.
and so…
I think this is the process needed to achieve required, though som
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 PRODUC
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 fro
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 shippe
to a particular region are posted to new worksheet, all data from tha
country which billed to the same region but shipped to a differen
region post to another worksheet, process repeats until all data ha
been reallocated to new worksheets by customer, bill to region, ship t
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