clear contents cells for specific sheets except last sheet when save new file

[leap out]

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

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

 

[OaklandJim]

You might get some assistance if you could post the workbook. Otherwise someone that wants to help has to recreate everything to try to put together code needed. Even if there is fake data in the workbook that would help. Also, maybe try to explain the need again, differenty, so it might be more clear.

You are allowed to post a workbook via a link to it on Dropbox or 1Drive. Use the link icon above.

 

[leap out]

Hi
here is the file
INVEN with single search v0 c (1).xlsm
but I no know what you mean

Also, maybe try to explain the need again, differenty, so it might be more clear.

my details are not clear ?

 

[OaklandJim]

Is this what the macro should do?

Goal: export file as xlsm named like this STOCK_2023 where 2023 is the current year. That file contains current Stock data that is data put into the Summary Sheet.
Details:
1. Keep the same named sheets with formatting.
2. Replace data that is in the summary sheet with data from the stock sheet.
3. Clear data from sheets a. sales, b. pur, c. RETURNS but keep the same formatting.

Should data transferred from SUMMARY be added to the bottom of the SUMMARY sheet or should it replace the data in the SUMMARY sheet?

Data in the STOCK sheet is not changed by the macro?

 

[leap out]

Should data transferred from SUMMARY be added to the bottom of the SUMMARY sheet or should it replace the data in the SUMMARY sheet?

should replace data .

Data in the STOCK sheet is not changed by the macro?

I'm not sure what you mean !
when run the macro should replace data in stock sheet if data change in summary sheet .

 

[OaklandJim]

I would like to help you but I have to understand what you want. And no your need details are not clear, maybe because I am not so smart.

To confirm...the main objective is to 1. clear data from the SUMMARY sheet 2. transfer data from the STOCK sheet to SUMMARY sheet, 3. clear data from the STOCK, sales, pur and RETURNS sheets, keep all formatting.

BUT, the STOCK and SUMMARY sheets have different columns. From your workbook,

Columns in STOCK are: ITEM, CODE, BRAND, TYPE, MANUFACTURE, QTY.

Columns in SUMMARY are: ITEM, CODE, BRAND, TYPE, MANUFACTURE, STOCK, SALES, PUR, RETURNS, BALANCE

So, to restate the need: Columns transferred from STOCK sheet to the SUMMARY sheet include ITEM, CODE, BRAND, TYPE, MANUFACTURE and data in the the other columns in SUMMARY sheet are cleared. Then data is cleared from the STOCK, sales, pur and RETURNS sheets, keeping all formatting.

 

[OaklandJim]

Also, I am guessing that data in column QTY in STOCK sheet are copied into column STOCK in the SUMMARY sheet.

 

[leap out]

1. clear data from the SUMMARY sheet

yes

2. transfer data from the STOCK sheet to SUMMARY sheet

no should transfer data from SUMMARY sheet to STOCK sheet (just see the last picture before result and the first picture for result )

BUT, the STOCK and SUMMARY sheets have different columns. From your workbook,

yes , don't change the headers , just clear from row 2 for every sheet.

Also, I am guessing that data in column QTY in STOCK sheet are copied into column STOCK in the SUMMARY sheet.

as I said in point2

 

[OaklandJim]

I guess you are frustrated with me for being so dumb. I am trying my best to understand what happens. Maybe there is a language problem?

Is THIS what the macro should do?

1. All data is cleared from STOCK, SALES, pur, RETURNs sheets keeping formatting and column headers.

2. Data from SUMMARY sheet -- including all 10 columns from the SUMMARY sheet -- is transferred to the STOCK sheet.

3. After data from SUMMARY sheet is transferred to the STOCK sheet then clear data from the SUMMARY sheet.

Note:
BEFORE the data from the SUMMARY sheet is transferred to the STOCK sheet there are six headers in the STOCK sheet: 1. item, 2. CODE, 3. BRAND, 4. TYPE, 5. MANUFACTURE, 6. QTY.

AFTER transferring data from SUMMARY sheet to STOCK sheet the STOCK sheet will then have 10 headers:
1. item, 2. CODE, 3. BRAND, 4. TYPE, 5. MANUFACTURE, 6. STOCK, 7. SALES, 8. PUR, 9. RETURNS, 10. BALANCE.

I hope that I understand now!?

 

[leap out]

yes all of your points are correct.

as to the note , my apologies!! a
bout different columns for STOCK sheet before and after despite of you made me attention more than one time but I don't answer you Thinking that all things are ok . I don't note it in OP , sorry !