abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 612
- Office Version
- 2019
Hello
I would split BRAND sheet into multiple sheets(BRAND1,BRAND2....) based on G2 cell(how many rows should be ignoring headers from numbers in G2 should show how many items for each sheet alone.
as example when write 4 then should show the headers in row1 and 4 items for each sheet and the last sheet will be 3 Items also insert TOTAL row to sum column F . if the G2 is empty doesn't do anything.
I don't want splitting sheets repeatedly when run the macro every time, just replace data .
result
I would split BRAND sheet into multiple sheets(BRAND1,BRAND2....) based on G2 cell(how many rows should be ignoring headers from numbers in G2 should show how many items for each sheet alone.
as example when write 4 then should show the headers in row1 and 4 items for each sheet and the last sheet will be 3 Items also insert TOTAL row to sum column F . if the G2 is empty doesn't do anything.
I don't want splitting sheets repeatedly when run the macro every time, just replace data .
| AA | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | numbers | ||
| 2 | 1 | 1221 | BS 205/70R15C R623 THI | 62.00 | 405.00 | 25,110.00 | 4 | ||
| 3 | 2 | 1227 | BS 215/70R15C R623 THI | 22.00 | 425.00 | 9,350.00 | |||
| 4 | 3 | 1227 | BS 215/70R15C R623 THI | 4.00 | 544.00 | 2,176.00 | |||
| 5 | 4 | 1241 | BS 1200R20 G580 JAP | 220.00 | 2,035.00 | 447,700.00 | |||
| 6 | 5 | 1284 | GC 1200R20 AZ0026 CHI | 80.00 | 895.00 | 71,600.00 | |||
| 7 | 6 | 1284 | GC 1200R20 AZ0026 CHI | 50.00 | 1,125.00 | 56,250.00 | |||
| 8 | 7 | 1285 | GC 1200R20 AZ0183 CHI | 40.00 | 925.00 | 37,000.00 | |||
| 9 | 8 | 1285 | GC 1200R20 AZ0183 CHI | 10.00 | 1,225.00 | 12,250.00 | |||
| 10 | 9 | 1285 | GC 1200R20 AZ0183 CHI | 60.00 | 1,205.00 | 72,300.00 | |||
| 11 | 10 | 1385 | GC 315/80R22.5 AT161 CHI | 20.00 | 735.00 | 14,700.00 | |||
| 12 | 11 | 1294 | GC 315/80R22.5 AZ188 CHI | 20.00 | 745.00 | 14,900.00 | |||
| 13 | 12 | 1287 | GC 315/80R22.5 AZ126 CHI | 60.00 | 735.00 | 44,100.00 | |||
| 14 | 13 | 1305 | BS 700R16 R230 JAP | 2.00 | 770.00 | 1,540.00 | |||
| 15 | 14 | 1306 | BS 750R16 R230 JAP | 10.00 | 775.00 | 7,750.00 | |||
| 16 | 15 | 1306 | BS 750R16 R230 JAP | 4.00 | 780.00 | 3,120.00 | |||
| 17 | TOTAL | 819,846.00 | |||||||
BRANDS | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F16 | F2 | =D2*E2 |
| F17 | F17 | =SUM(F2:F16) |
result
| AA | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 1221 | BS 205/70R15C R623 THI | 62.00 | 405.00 | 25,110.00 | ||
| 3 | 2 | 1227 | BS 215/70R15C R623 THI | 22.00 | 425.00 | 9,350.00 | ||
| 4 | 3 | 1227 | BS 215/70R15C R623 THI | 4.00 | 544.00 | 2,176.00 | ||
| 5 | 4 | 1241 | BS 1200R20 G580 JAP | 220.00 | 2,035.00 | 447,700.00 | ||
| 6 | TOTAL | 484,336.00 | ||||||
BRAND1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F5 | F2 | =D2*E2 |
| F6 | F6 | =SUM(F2:F5) |
| AA | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 1284 | GC 1200R20 AZ0026 CHI | 80.00 | 895.00 | 71,600.00 | ||
| 3 | 2 | 1284 | GC 1200R20 AZ0026 CHI | 50.00 | 1,125.00 | 56,250.00 | ||
| 4 | 3 | 1285 | GC 1200R20 AZ0183 CHI | 40.00 | 925.00 | 37,000.00 | ||
| 5 | 4 | 1285 | GC 1200R20 AZ0183 CHI | 10.00 | 1,225.00 | 12,250.00 | ||
| 6 | TOTAL | 177,100.00 | ||||||
BRAND2 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F5 | F2 | =D2*E2 |
| F6 | F6 | =SUM(F2:F5) |
| AA | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 1285 | GC 1200R20 AZ0183 CHI | 60.00 | 1,205.00 | 72,300.00 | ||
| 3 | 2 | 1385 | GC 315/80R22.5 AT161 CHI | 20.00 | 735.00 | 14,700.00 | ||
| 4 | 3 | 1294 | GC 315/80R22.5 AZ188 CHI | 20.00 | 745.00 | 14,900.00 | ||
| 5 | 4 | 1287 | GC 315/80R22.5 AZ126 CHI | 60.00 | 735.00 | 44,100.00 | ||
| 6 | TOTAL | 146,000.00 | ||||||
BRAND3 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F5 | F2 | =D2*E2 |
| F6 | F6 | =SUM(F2:F5) |
| AA | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 1305 | BS 700R16 R230 JAP | 2.00 | 770.00 | 1,540.00 | ||
| 3 | 2 | 1306 | BS 750R16 R230 JAP | 10.00 | 775.00 | 7,750.00 | ||
| 4 | 3 | 1306 | BS 750R16 R230 JAP | 4.00 | 780.00 | 3,120.00 | ||
| 5 | TOTAL | 12,410.00 | ||||||
BRAND4 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F4 | F2 | =D2*E2 |
| F5 | F5 | =SUM(F2:F4) |
Last edited:
