Hi
I want to export file as xlsm with keep the same sheets names and formatting , but should replace data form summary sheet with stock sheet and clear sheets (sales,pur,RETURNS,SUMMARY) with keep the same formatting and when save the file should be file name STOCK-& current year . and should replace data every time run the macro based on current year.
original data
result should save file as xlsm
I hope this make sense
thanks
I want to export file as xlsm with keep the same sheets names and formatting , but should replace data form summary sheet with stock sheet and clear sheets (sales,pur,RETURNS,SUMMARY) with keep the same formatting and when save the file should be file name STOCK-& current year . and should replace data every time run the macro based on current year.
original data
INVEN with single search v0 c.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | item | CODE | BRAND | TYPE | MANUFACTURE | QTY | ||
2 | 1 | AA-1 | 10W40 208L | Q8 | EU | 2222 | ||
3 | 2 | AA-2 | 15W40 208L | CAS | SU | 400 | ||
4 | 3 | AA-3 | 5W30 208L | Q8 | EU | 800 | ||
5 | 4 | AA-4 | 5W30 12x1L | Q8 | EU | 600 | ||
6 | 5 | AA-5 | 10W40 208L | ENI | IT | 300 | ||
7 | 6 | AA-6 | 5W30 4x4L | Q8 | EU | 200 | ||
8 | 7 | AA-7 | 10W40 12x1L | Q8 | EU | 120 | ||
9 | 8 | AA-8 | 15W40 12x1L | CAS | SU | 450 | ||
10 | 9 | AA-9 | 10W40 12x1L | ENI | IT | 890 | ||
11 | 10 | AA-10 | 10W40 4x4L | Q8 | EU | 345 | ||
12 | 11 | AA-11 | 10W40 4x4L | CAS | SU | 78 | ||
13 | 12 | AA-12 | 10W40 4x4L | ENI | IT | 123 | ||
14 | 13 | AA-13 | 5W40 4x4L | Q8 | EU | 456 | ||
15 | 14 | AA-14 | 5W40 4x4L | CAS | SU | 678 | ||
16 | 15 | AA-15 | 5W40 4x4L | ENI | IT | 1234 | ||
17 | 16 | AA-16 | 20W50 4x4L | Q8 | EU | 456 | ||
STOCK |
INVEN with single search v0 c.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CODE | BRAND | TYPE | MANUFACTURE | SALES | ||
2 | 01/01/2021 | AA-1 | 10W40 208L | Q8 | EU | 100 | ||
3 | 02/01/2021 | AA-2 | 15W40 208L | CAS | SU | 50 | ||
4 | 03/01/2021 | AA-3 | 5W30 208L | Q8 | EU | 280 | ||
5 | 04/01/2021 | AA-4 | 5W30 12x1L | Q8 | EU | 300 | ||
6 | 05/01/2021 | AA-5 | 10W40 208L | ENI | IT | 80 | ||
7 | 06/01/2021 | AA-6 | 5W30 4x4L | Q8 | EU | 20 | ||
8 | 07/01/2021 | AA-7 | 10W40 12x1L | Q8 | EU | 20 | ||
9 | 08/01/2021 | AA-8 | 15W40 12x1L | CAS | SU | 20 | ||
10 | 09/01/2021 | AA-9 | 10W40 12x1L | ENI | IT | 876 | ||
11 | 10/01/2021 | AA-10 | 10W40 4x4L | Q8 | EU | 345 | ||
12 | 11/01/2021 | AA-11 | 10W40 4x4L | CAS | SU | 123 | ||
13 | 12/01/2021 | AA-12 | 10W40 4x4L | ENI | IT | 78 | ||
14 | 13/01/2021 | AA-13 | 5W40 4x4L | Q8 | EU | 300 | ||
15 | 14/01/2021 | AA-14 | 5W40 4x4L | CAS | SU | 34 | ||
16 | 15/01/2021 | AA-15 | 5W40 4x4L | ENI | IT | 23 | ||
17 | 16/01/2021 | AA-16 | 20W50 4x4L | Q8 | EU | 56 | ||
18 | 17/01/2021 | AA-1 | 10W40 208L | Q8 | EU | 100 | ||
sales |
INVEN with single search v0 c.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CODE | BRAND | TYPE | MANUFACTURE | PURCHASE | ||
2 | 04/02/2021 | AA-1 | 10W40 208L | Q8 | EU | 55 | ||
3 | 05/02/2021 | AA-2 | 15W40 208L | CAS | SU | 20 | ||
4 | 06/02/2021 | AA-3 | 5W30 208L | Q8 | EU | 10 | ||
5 | 07/02/2021 | AA-4 | 5W30 12x1L | Q8 | EU | 10 | ||
6 | 08/02/2021 | AA-5 | 10W40 208L | ENI | IT | 3 | ||
7 | 09/02/2021 | AA-6 | 5W30 4x4L | Q8 | EU | 4 | ||
8 | 10/02/2021 | AA-7 | 10W40 12x1L | Q8 | EU | 45 | ||
9 | 11/02/2021 | AA-8 | 15W40 12x1L | CAS | SU | 8 | ||
10 | 12/02/2021 | AA-9 | 10W40 12x1L | ENI | IT | 1 | ||
11 | 13/02/2021 | AA-10 | 10W40 4x4L | Q8 | EU | 100 | ||
12 | 14/02/2021 | AA-11 | 10W40 4x4L | CAS | SU | 20 | ||
13 | 15/02/2021 | AA-12 | 10W40 4x4L | ENI | IT | 100 | ||
14 | 16/02/2021 | AA-13 | 5W40 4x4L | Q8 | EU | 44 | ||
15 | 17/02/2021 | AA-14 | 5W40 4x4L | CAS | SU | 20 | ||
16 | 18/02/2021 | AA-15 | 5W40 4x4L | ENI | IT | 50 | ||
17 | 19/02/2021 | AA-16 | 20W50 4x4L | Q8 | EU | 12 | ||
18 | 20/02/2021 | AA-17 | 20W50 4x4L | CAS | SU | 9 | ||
19 | 21/02/2021 | AA-18 | 20W50 4x4L | ENI | IT | 4 | ||
20 | 22/02/2021 | AA-1 | 10W40 208L | Q8 | EU | 55 | ||
pur |
INVEN with single search v0 c.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | item | CODE | BRAND | TYPE | MANUFACTURE | returns | ||
2 | 05/04/2021 | AA-9 | 10W40 12x1L | ENI | IT | 20 | ||
3 | 06/04/2021 | AA-10 | 10W40 4x4L | Q8 | EU | 30 | ||
4 | 07/04/2021 | AA-11 | 10W40 4x4L | CAS | SU | 40 | ||
5 | 08/04/2021 | AA-4 | 5W30 12x1L | Q8 | EU | 10 | ||
6 | 09/04/2021 | AA-4 | 5W30 12x1L | Q8 | EU | 11 | ||
RETURNS |
INVEN with single search v0 c.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | item | CODE | BRAND | TYPE | MANUFACTURE | STOCK | SALES | PUR | RETURNS | BALANCE | ||
2 | 1 | AA-1 | 10W40 208L | Q8 | EU | 2222 | 200 | 110 | 2132 | |||
3 | 2 | AA-2 | 15W40 208L | CAS | SU | 400 | 50 | 20 | 370 | |||
4 | 3 | AA-3 | 5W30 208L | Q8 | EU | 800 | 280 | 10 | 530 | |||
5 | 4 | AA-4 | 5W30 12x1L | Q8 | EU | 600 | 300 | 10 | 21 | 331 | ||
6 | 5 | AA-5 | 10W40 208L | ENI | IT | 300 | 80 | 3 | 223 | |||
7 | 6 | AA-6 | 5W30 4x4L | Q8 | EU | 200 | 20 | 4 | 184 | |||
8 | 7 | AA-7 | 10W40 12x1L | Q8 | EU | 120 | 20 | 45 | 145 | |||
9 | 8 | AA-8 | 15W40 12x1L | CAS | SU | 450 | 20 | 8 | 438 | |||
10 | 9 | AA-9 | 10W40 12x1L | ENI | IT | 890 | 876 | 1 | 20 | 35 | ||
11 | 10 | AA-10 | 10W40 4x4L | Q8 | EU | 345 | 345 | 100 | 30 | 130 | ||
12 | 11 | AA-11 | 10W40 4x4L | CAS | SU | 78 | 123 | 20 | 40 | 15 | ||
13 | 12 | AA-12 | 10W40 4x4L | ENI | IT | 123 | 78 | 100 | 145 | |||
14 | 13 | AA-13 | 5W40 4x4L | Q8 | EU | 456 | 300 | 44 | 200 | |||
15 | 14 | AA-14 | 5W40 4x4L | CAS | SU | 678 | 34 | 20 | 664 | |||
16 | 15 | AA-15 | 5W40 4x4L | ENI | IT | 1234 | 23 | 50 | 1261 | |||
17 | 16 | AA-16 | 20W50 4x4L | Q8 | EU | 456 | 56 | 12 | 412 | |||
18 | 17 | AA-17 | 20W50 4x4L | CAS | SU | 9 | 9 | |||||
19 | 18 | AA-18 | 20W50 4x4L | ENI | IT | 4 | 4 | |||||
SUMMARY |
result should save file as xlsm
STOCK-2023.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | item | CODE | BRAND | TYPE | MANUFACTURE | STOCK | SALES | PUR | RETURNS | BALANCE | ||
2 | 1 | AA-1 | 10W40 208L | Q8 | EU | 2222 | 200 | 110 | 2132 | |||
3 | 2 | AA-2 | 15W40 208L | CAS | SU | 400 | 50 | 20 | 370 | |||
4 | 3 | AA-3 | 5W30 208L | Q8 | EU | 800 | 280 | 10 | 530 | |||
5 | 4 | AA-4 | 5W30 12x1L | Q8 | EU | 600 | 300 | 10 | 21 | 331 | ||
6 | 5 | AA-5 | 10W40 208L | ENI | IT | 300 | 80 | 3 | 223 | |||
7 | 6 | AA-6 | 5W30 4x4L | Q8 | EU | 200 | 20 | 4 | 184 | |||
8 | 7 | AA-7 | 10W40 12x1L | Q8 | EU | 120 | 20 | 45 | 145 | |||
9 | 8 | AA-8 | 15W40 12x1L | CAS | SU | 450 | 20 | 8 | 438 | |||
10 | 9 | AA-9 | 10W40 12x1L | ENI | IT | 890 | 876 | 1 | 20 | 35 | ||
11 | 10 | AA-10 | 10W40 4x4L | Q8 | EU | 345 | 345 | 100 | 30 | 130 | ||
12 | 11 | AA-11 | 10W40 4x4L | CAS | SU | 78 | 123 | 20 | 40 | 15 | ||
13 | 12 | AA-12 | 10W40 4x4L | ENI | IT | 123 | 78 | 100 | 145 | |||
14 | 13 | AA-13 | 5W40 4x4L | Q8 | EU | 456 | 300 | 44 | 200 | |||
15 | 14 | AA-14 | 5W40 4x4L | CAS | SU | 678 | 34 | 20 | 664 | |||
16 | 15 | AA-15 | 5W40 4x4L | ENI | IT | 1234 | 23 | 50 | 1261 | |||
17 | 16 | AA-16 | 20W50 4x4L | Q8 | EU | 456 | 56 | 12 | 412 | |||
18 | 17 | AA-17 | 20W50 4x4L | CAS | SU | 9 | 9 | |||||
19 | 18 | AA-18 | 20W50 4x4L | ENI | IT | 4 | 4 | |||||
STOCK |
STOCK-2023.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CODE | BRAND | TYPE | MANUFACTURE | SALES | ||
2 | ||||||||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | ||||||||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
11 | ||||||||
12 | ||||||||
13 | ||||||||
14 | ||||||||
15 | ||||||||
16 | ||||||||
17 | ||||||||
18 | ||||||||
sales |
STOCK-2023.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CODE | BRAND | TYPE | MANUFACTURE | PURCHASE | ||
2 | ||||||||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | ||||||||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
11 | ||||||||
12 | ||||||||
13 | ||||||||
14 | ||||||||
15 | ||||||||
16 | ||||||||
17 | ||||||||
18 | ||||||||
19 | ||||||||
20 | ||||||||
pur |
STOCK-2023.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | item | CODE | BRAND | TYPE | MANUFACTURE | returns | ||
2 | ||||||||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | ||||||||
RETURNS |
STOCK-2023.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | item | CODE | BRAND | TYPE | MANUFACTURE | STOCK | SALES | PUR | RETURNS | BALANCE | ||
2 | ||||||||||||
3 | ||||||||||||
4 | ||||||||||||
5 | ||||||||||||
6 | ||||||||||||
7 | ||||||||||||
8 | ||||||||||||
9 | ||||||||||||
10 | ||||||||||||
11 | ||||||||||||
12 | ||||||||||||
13 | ||||||||||||
14 | ||||||||||||
15 | ||||||||||||
16 | ||||||||||||
17 | ||||||||||||
18 | ||||||||||||
19 | ||||||||||||
SUMMARY |
I hope this make sense
thanks
Last edited: