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

 

[HaHoBe]

Hi MKLAQ,

maybe to start with

Public Sub MrE_1230300_1703110()
' https://www.mrexcel.com/board/threads/clear-data-from-specific-sheets-and-move-data-from-the-last-sheet-to-the-first-sheet.1230300/
Dim strSheetsClear As String
Dim lngCounter As Long
Dim varSplit As Variant
Dim varArr As Variant

strSheetsClear = "***,ATR,BTS,SUMMARY"

With Sheets("Summary").UsedRange
  varArr = .Offset(1).Resize(.Rows.Count - 1)
End With

For lngCounter = 1 To 5
  Sheets("ATS").Cells(2, lngCounter).Resize(UBound(varArr, 1), 1).Value = Application.Index(varArr, 0, lngCounter)
Next lngCounter
With Sheets("ATS").Range("F2").Resize(UBound(varArr, 1), 1)
  .Value = Application.Index(varArr, 0, 10)
  .Offset(, 1).Value = ""
End With

varSplit = Split(strSheetsClear, ",")
For lngCounter = LBound(varSplit) To UBound(varSplit)
  With Sheets(varSplit(lngCounter)).UsedRange
    .Offset(1).Resize(.Rows.Count - 1).ClearContents
  End With
Next lngCounter
End Sub

Ciao,
Holger

 

[MKLAQ]

thanks
gives error subscript out of range in this line

  With Sheets(varSplit(lngCounter)).UsedRange

 

[HaHoBe]

Hi MKLAQ,

codeline

strSheetsClear = "***,ATR,BTS,SUMMARY"

should be

strSheetsClear = "A??,ATR,BTS,SUMMARY"

where ? must be replaced by S. It seems that the Forum Software has these three letters on the Blocklist as the content will only be changed on replying.

Holger

 

[MKLAQ]

It seems that the Forum Software has these three letters on the Blocklist as the content will only be changed on replying.

I was asking about stars . so should be ATS . even that still the error shows .

 

[HaHoBe]

Hi MKLAQ,

ATS was never part of the array as you wanted a "special" treatment to that sheet which would omitt the sheet from being cleared, I was talking about A S S which was the second sheet in the list you featured in the opening post.

Holger

 

[MKLAQ]

Perfect !
just I'm afraid after run the code and clearing data I press the macro again ,then will clear data for first sheet and losing data .
is there any way to keep data in first sheet after clear data for others sheets even if I run macro many time?

 

[HaHoBe]

Hi MKLAQ,

you could place a boolean value in a cell on sheet ATS outside the range on 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:

Public Sub MrE_1230300_1703110()
' https://www.mrexcel.com/board/threads/clear-data-from-specific-sheets-and-move-data-from-the-last-sheet-to-the-first-sheet.1230300/
Dim strSheetsClear As String
Dim lngCounter As Long
Dim varSplit As Variant
Dim varArr As Variant

'checking the value if macro should be run
If UCase(Sheets("ATS").Range("N1").Value) = "TRUE" Then
  
'  strSheetsClear = "***,ATR,BTS,SUMMARY"
  strSheetsClear = "A??,ATR,BTS,SUMMARY"
  
  With Sheets("Summary").UsedRange
    varArr = .Offset(1).Resize(.Rows.Count - 1)
  End With
  
  For lngCounter = 1 To 5
    Sheets("ATS").Cells(2, lngCounter).Resize(UBound(varArr, 1), 1).Value = Application.Index(varArr, 0, lngCounter)
  Next lngCounter
  With Sheets("ATS").Range("F2").Resize(UBound(varArr, 1), 1)
    .Value = Application.Index(varArr, 0, 10)
    .Offset(, 1).Value = ""
  End With
  
  varSplit = Split(strSheetsClear, ",")
  For lngCounter = LBound(varSplit) To UBound(varSplit)
    With Sheets(varSplit(lngCounter)).UsedRange
      .Offset(1).Resize(.Rows.Count - 1).ClearContents
    End With
  Next lngCounter
  'set value to skip macro if value isn*t changed manually
  Sheets("ATS").Range("N1").Value = "False"
End If
End Sub

Ciao,
Holger

 

[MKLAQ]

Hi HaHoBe
the code doesn't do anything !

 

[HaHoBe]

Hi MKLAQ,

what is the content of N1 on sheet "ATS"? If it is not true (written in either way) the code will do nothing.

Holger