Clear data from specific sheets and move data from the last sheet to the first sheet

[MKLAQ]

Hi
I want clearing data for specific sheets , not all of the sheets in workbook because of I have others sheets shouldn't clear data .
so what I want clearing data for theses sheets and before clear data for summary sheet should move data except columns F,G,H,I to ATS sheet .

Clear sheetss.xlsm
	A	B	C	D	E	F	G
1	ITEM	ID	BRAND	BTM	BTS	BUYING	SELLING
2	1	CCR1-CMB1	C-BM-100	BBS-001	BTR1-001	233.00	100.00
3	2	CCR1-CMB2	C-BM-101	BBS-002	BTR1-002	123.00
4	3	CCR1-CMB3	C-BM-102	BBS-003	BTR1-003	124.00
5	4	CCR1-CMB4	C-BM-103	BBS-004	BTR1-004	123.00	2.00
6	5	CCR1-CMB5	C-BM-104	BBS-005	BTR1-005	44.00	3.00
7	6	CCR1-CMB6	C-BM-105	BBS-006	BTR1-006	55.00	4.00
8	7	CCR1-CMB7	C-BM-106	BBS-007	BTR1-007	66.00
9	8	CCR1-CMB8	C-BM-107	BBS-008	BTR1-008	77.00	700.00
10	9	CCR1-CMB9	C-BM-108	BBS-009	BTR1-009	88.00	12.00
ATS

Clear sheetss.xlsm
	F	G	H	I	J	K	L	M
1	ITEM	ID	BRAND	BTM	BTS	BUYING	RETURNS	NET
2	1	CCR1-CMB4	C-BM-103	BBS-004	BTR1-004	233.00	100.00	133.00
3	2	CCR1-CMB5	C-BM-104	BBS-005	BTR1-005	123.00		123.00
4	3	CCR1-CMB6	C-BM-105	BBS-006	BTR1-006	124.00		124.00
5	4	CCR1-CMB7	C-BM-106	BBS-007	BTR1-007	123.00	2.00	121.00
6	5	CCR1-CMB8	C-BM-107	BBS-008	BTR1-008	44.00	3.00	41.00
7	6	CCR1-CMB9	C-BM-108	BBS-009	BTR1-009	55.00	4.00	51.00
8	7	CCR1-CMB10	C-BM-109	BBS-010	BTR1-010	66.00		66.00
9	8	CCR1-CMB11	C-BM-110	BBS-011	BTR1-011	77.00		77.00
10	9	CCR1-CMB12	C-BM-111	BBS-012	BTR1-012	88.00	12.00	76.00
ASS

Clear sheetss.xlsm
	E	F	G	H	I	J
1	ITEM	ID	BRAND	BTM	BTS	BUYING
2	1	CCR1-CMB41	C-BM-112	BBS-013	BTR1-013	200.00
3	2	CCR1-CMB42	C-BM-113	BBS-014	BTR1-014	120.00
4	3	CCR1-CMB43	C-BM-114	BBS-015	BTR1-015	100.00
5	4	CCR1-CMB44	C-BM-115	BBS-016	BTR1-016	110.00
6	5	CCR1-CMB45	C-BM-116	BBS-017	BTR1-017	3.00
7	6	CCR1-CMB9	C-BM-108	BBS-009	BTR1-009	4.00
8	7	CCR1-CMB10	C-BM-109	BBS-010	BTR1-010	11.00
9	8	CCR1-CMB11	C-BM-110	BBS-011	BTR1-011	11.00
10	9	CCR1-CMB12	C-BM-111	BBS-012	BTR1-012	12.00
ATR

Clear sheetss.xlsm
	B	C	D	E	F	G
1	ITEM	ID	BRAND	BTM	BTS	SELLING
2	1	CCR1-CMB41	C-BM-100	BBS-013	BTR1-013	200.00
3	2	CCR1-CMB42	C-BM-101	BBS-014	BTR1-014	120.00
4	3	CCR1-CMB43	C-BM-102	BBS-015	BTR1-015	100.00
5	4	CCR1-CMB44	C-BM-103	BBS-016	BTR1-016	110.00
6	5	CCR1-CMB45	C-BM-104	BBS-017	BTR1-017	3.00
BTS

Clear sheetss.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	ID	BRAND	BTM	BTS	BUYING	SELLING	RS	RB	NET
2	1	CCR1-CMB1	C-BM-100	BBS-001	BTR1-001	233.00	100.00	12.00		145.00
3	2	CCR1-CMB2	C-BM-101	BBS-002	BTR1-002	123.00		11.00	11.00	123.00
4	3	CCR1-CMB3	C-BM-102	BBS-003	BTR1-003	124.00	11.00	11.00	11.00	113.00
5	4	CCR1-CMB4	C-BM-103	BBS-004	BTR1-004	123.00	2.00		11.00	110.00
6	5	CCR1-CMB5	C-BM-104	BBS-005	BTR1-005	44.00	3.00			41.00
7	6	CCR1-CMB6	C-BM-105	BBS-006	BTR1-006	55.00	4.00			51.00
8	7	CCR1-CMB7	C-BM-106	BBS-007	BTR1-007	66.00		1.00		67.00
9	8	CCR1-CMB8	C-BM-107	BBS-008	BTR1-008	77.00	12.00	2.00		67.00
10	9	CCR1-CMB9	C-BM-108	BBS-009	BTR1-009	88.00	12.00			76.00
SUMMARY

result

Clear sheetss.xlsm
	A	B	C	D	E	F	G
1	ITEM	ID	BRAND	BTM	BTS	BUYING	SELLING
2	1	CCR1-CMB1	C-BM-100	BBS-001	BTR1-001	145.00
3	2	CCR1-CMB2	C-BM-101	BBS-002	BTR1-002	123.00
4	3	CCR1-CMB3	C-BM-102	BBS-003	BTR1-003	113.00
5	4	CCR1-CMB4	C-BM-103	BBS-004	BTR1-004	110.00
6	5	CCR1-CMB5	C-BM-104	BBS-005	BTR1-005	41.00
7	6	CCR1-CMB6	C-BM-105	BBS-006	BTR1-006	51.00
8	7	CCR1-CMB7	C-BM-106	BBS-007	BTR1-007	67.00
9	8	CCR1-CMB8	C-BM-107	BBS-008	BTR1-008	67.00
10	9	CCR1-CMB9	C-BM-108	BBS-009	BTR1-009	76.00
ATS

Clear sheetss.xlsm
	F	G	H	I	J	K	L	M
1	ITEM	ID	BRAND	BTM	BTS	BUYING	RETURNS	NET
2
3
4
5
6
7
8
9
10
ASS

Clear sheetss.xlsm
	E	F	G	H	I	J
1	ITEM	ID	BRAND	BTM	BTS	BUYING
2
3
4
5
6
7
8
9
10
ATR

Clear sheetss.xlsm
	B	C	D	E	F	G
1	ITEM	ID	BRAND	BTM	BTS	SELLING
2
3
4
5
6
BTS

Clear sheetss.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	ID	BRAND	BTM	BTS	BUYING	SELLING	RS	RB	NET
2
3
4
5
6
7
8
9
10
SUMMARY

thanks

 

[MKLAQ]

I don't understand !
what you mean N1 , is it cell ?
there is no column N in ATS sheet at all.

 

[HaHoBe]

Hi MKLAQ,

there is no column N in ATS sheet at all

Even the first versions of Excel offered 256 Columns which went from Column A to IV including column N.

you could place a boolean value in a cell on sheet ATS outside the range in question for work which holds either True (only then the macro will run) or False (skip it) - I used a Data Validation list in that cell to only allow these values in my workbook

In the code I posted that is the cell with the address N1 on the sheeet ATS - well outside the range that you posted in order not to interfere with the code.

Holger

 

[MKLAQ]

sorry !
I was hurry up to see how code works without check the code .
thanks very much for your answering