hi
I have many sheets contain data and values for sales and purchase
in sheet report has also data . what I want insert three columns with the same borders and formatting contain (PURCHASE,SALES,BALANCE) and summing the values for SALES , PURCHASE across multiple sheets and put the values in sheet REPORT in COLUMN SALES & PURCHASE . the COL BALANCE should add the formula as I put in sheet CASE1 when run the macro from the first time but when I run again . the formula will be change as I put in COL BALANCE in sheet CASE2 and somtimes there are some a new items in all the sheets except the sheet REPORT then should add it as I highlighted in sheet REPORT . when get the values after summing the values across sheets should match COL B,C,D toghether and when add a new items in sheet REPORT should match COL A with the others sheets in COL A
NOTE: the sheet CASE1,2 just to understand what I want . the result should be in sheet REPORT and the formula in CASE2 it will continue every time run the macro .
also issued in this forum macro insert three columns repeatedly with all values based on multiple sheets
first sheet
second sheet
third sheet
before run the macro how was it in this sheet
the expected result in sheet REPORT for two cases
case1
case 2
I hope finding answer in this forum despite this project is very coplicated
I have many sheets contain data and values for sales and purchase
in sheet report has also data . what I want insert three columns with the same borders and formatting contain (PURCHASE,SALES,BALANCE) and summing the values for SALES , PURCHASE across multiple sheets and put the values in sheet REPORT in COLUMN SALES & PURCHASE . the COL BALANCE should add the formula as I put in sheet CASE1 when run the macro from the first time but when I run again . the formula will be change as I put in COL BALANCE in sheet CASE2 and somtimes there are some a new items in all the sheets except the sheet REPORT then should add it as I highlighted in sheet REPORT . when get the values after summing the values across sheets should match COL B,C,D toghether and when add a new items in sheet REPORT should match COL A with the others sheets in COL A
NOTE: the sheet CASE1,2 just to understand what I want . the result should be in sheet REPORT and the formula in CASE2 it will continue every time run the macro .
also issued in this forum macro insert three columns repeatedly with all values based on multiple sheets
first sheet
COLLECTION.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | CODE | BR | TY | OR | PURCHASE | SALES | ||
2 | FR-00 | FR | BANANA | TT | 200 | 10 | ||
3 | FR | APPLE | LL | 100 | 20 | |||
4 | FR | PEAR | NN | 10 | - | |||
5 | FR | BANANA | 20 | - | ||||
6 | VEG-00 | VEG | TOMATO | SS | 12 | - | ||
7 | VEG | TOMATO | AA | 12 | 12 | |||
8 | FO-00 | TUNA | 180G | TH | 20 | 5 | ||
9 | TUNA | 180G | IND | 10 | ||||
SAS |
second sheet
COLLECTION.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | CODE | BR | TY | OR | SALES | ||
2 | FR-00 | FR | BANANA | TT | 5 | ||
3 | FR | APPLE | LL | 2 | |||
4 | FR | PEAR | NN | 3 | |||
5 | FR | BANANA | - | ||||
6 | VEG-00 | VEG | TOMATO | SS | 5 | ||
SS |
third sheet
COLLECTION.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | CODE | BR | TY | OR | PURCHASE | ||
2 | FR-00 | FR | APPLE | LL | 120 | ||
3 | FR | PEAR | NN | 30 | |||
4 | FR | BANANA | 40 | ||||
5 | VEG-00 | VEG | TOMATO | SS | 50 | ||
6 | VEG | TOMATO | AA | 5 | |||
7 | VEG | ONION | AA1 | 6 | |||
8 | VEG | POTATO | AA2 | 7 | |||
SR |
before run the macro how was it in this sheet
COLLECTION.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ||||||
2 | CODE | BR | TY | OR | ||
3 | FR-00 | FR | BANANA | TT | ||
4 | FR | APPLE | LL | |||
5 | FR | PEAR | NN | |||
6 | FR | BANANA | ||||
7 | TOT | |||||
8 | VEG-00 | VEG | TOMATO | SS | ||
9 | VEG | TOMATO | AA | |||
10 | VEG | ONION | AA1 | |||
11 | TOT | |||||
REPORT |
the expected result in sheet REPORT for two cases
case1
COLLECTION.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | JAN | ||||||||
2 | CODE | BR | TY | OR | PURCHASE | SALES | BALANCE | ||
3 | FR-00 | FR | BANANA | TT | 320 | 15 | 305 | ||
4 | FR | APPLE | LL | 100 | 22 | 78 | |||
5 | FR | PEAR | NN | 40 | 3 | 37 | |||
6 | FR | BANANA | 60 | - | |||||
7 | TOT | 520 | 40 | 420 | |||||
8 | VEG-00 | VEG | TOMATO | SS | 67 | 5 | 62 | ||
9 | VEG | TOMATO | AA | 17 | 12 | 5 | |||
10 | VEG | ONION | AA1 | 6 | - | 6 | |||
11 | VEG | POTATO | AA2 | 7 | - | 7 | |||
12 | TOT | 97 | 17 | 80 | |||||
13 | FO-00 | TUNA | 180G | TH | 20 | 5 | 15 | ||
14 | TUNA | 180G | IND | 10 | 0 | 10 | |||
15 | TOT | 30 | 5 | 25 | |||||
CASE1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G13:G14,G8:G11,G3:G5 | G3 | =E3-F3 |
E12:G12,E7:G7 | E7 | =SUM(E3:E6) |
E15:G15 | E15 | =SUM(E13:E14) |
case 2
COLLECTION.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | JAN | FEB | ||||||||||
2 | CODE | BR | TY | OR | PURCHASE | SALES | BALANCE | PURCHASE | SALES | BALANCE | ||
3 | FR-00 | FR | BANANA | TT | 320 | 15 | 305 | 320 | 15 | 610 | ||
4 | FR | APPLE | LL | 100 | 22 | 78 | 100 | 22 | 156 | |||
5 | FR | PEAR | NN | 40 | 3 | 37 | 40 | 3 | 74 | |||
6 | FR | BANANA | 60 | - | 60 | 60 | - | 120 | ||||
7 | TOT | 520 | 40 | 480 | 520 | 40 | 960 | |||||
8 | VEG-00 | VEG | TOMATO | SS | 67 | 5 | 62 | 67 | 5 | 124 | ||
9 | VEG | TOMATO | AA | 17 | 12 | 5 | 17 | 12 | 10 | |||
10 | VEG | ONION | AA1 | 6 | - | 6 | 6 | - | 12 | |||
11 | VEG | POTATO | AA2 | 7 | - | 7 | 7 | - | 14 | |||
12 | TOT | 97 | 17 | 80 | 97 | 17 | 160 | |||||
13 | FO-00 | TUNA | 180G | TH | 20 | 5 | 15 | 20 | 5 | 30 | ||
14 | TUNA | 180G | IND | 10 | 0 | 10 | 10 | 0 | 20 | |||
15 | TOT | 30 | 5 | 25 | 30 | 5 | 50 | |||||
CASE2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E12:J12,E7:J7 | E7 | =SUM(E3:E6) |
G13:G14,G8:G11,G3:G6 | G3 | =E3-F3 |
J13:J14,J8:J11,J3:J6 | J3 | =G3+H3-I3 |
E15:J15 | E15 | =SUM(E13:E14) |
I hope finding answer in this forum despite this project is very coplicated