Copy filtered items to new sheet

[doriannjeshi]

Hi,
I need a macro to filter and copy items to a new sheet. The filtered items and the new sheet name to be established in the macro.

Book2
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	june	3			-3	1
3	june	3	1	1	1	1
4	april	3	1		1	1
5	may	3	1	1	1	1
6	february	3	1	1
7	october	3	1	1
8	october	3	1	1
9	november	3	1	1
10	december	3	1	1
11
12
13	result
14	A	B	C	D	E	F
15	june	3			-3	1
16	june	3	1	1	1	1
17	may	3	1	1	1	1
18	october	3	1	1
19	october	3	1	1
20	sheet name"months"
sheet1

 

[MikeVol]

Hi @doriannjeshi. Very interesting but nothing clear. By what criteria are you going to filter the range? There is no logic in your example. Please explain in more detail (I am not taking on the task).

 

[HighAndWilder]

Hi,
I need a macro to filter and copy items to a new sheet. The filtered items and the new sheet name to be established in the macro.

Book2
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	june	3			-3	1
3	june	3	1	1	1	1
4	april	3	1		1	1
5	may	3	1	1	1	1
6	february	3	1	1
7	october	3	1	1
8	october	3	1	1
9	november	3	1	1
10	december	3	1	1
11
12
13	result
14	A	B	C	D	E	F
15	june	3			-3	1
16	june	3	1	1	1	1
17	may	3	1	1	1	1
18	october	3	1	1
19	october	3	1	1
20	sheet name"months"
sheet1

Try this.

This is similar to your recent post.

The required changes are indicated.

Public Sub subCopyFilteredItemsToNewSheet()
Dim Ws As Worksheet
  
  ActiveWorkbook.Save
  
  Set Ws = Sheets("Sheet1") ' <- Change to the sheet where the data is.
  
  Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("NewSheet").Delete ' <- Change to the new sheet name.
  On Error GoTo 0
  Application.DisplayAlerts = True
  
  With Ws
  
    .Range("A1").AutoFilter 2, 3 ' <- Change to the column number from the 2 and to the value from the 3.
  
    Sheets.Add(, Sheets(Sheets.Count)).Name = "NewSheet" ' <- Change to the new sheet name.
    
    .AutoFilter.Range.EntireRow.Copy Range("A1")
  
    .AutoFilterMode = False
  
  End With
   
End Sub

 

[doriannjeshi]

Sorry I wasn't clear
The months are to be filtered and copied and to be specified which ones in the macro

 

[HighAndWilder]

Sorry I wasn't clear
The months are to be filtered and copied and to be specified which ones in the macro

Is column A data a date formatted to show the month or just text?

 

[MikeVol]

A correct statement of the problem implies a correct and optimal solution. But your description is not like that.

 

[HighAndWilder]

Sorry I wasn't clear
The months are to be filtered and copied and to be specified which ones in the macro

Note where the changes are required.

Public Sub subCopyFilteredItemsToNewSheet()
Dim Ws As Worksheet
Dim strMonths As String
Dim strNewSheet As String

  ActiveWorkbook.Save
  
  Set Ws = Sheets("Sheet1") ' <- Change to the sheet where the data is.
  
  strMonths = "January,February,March,December" ' <- Change to the required months.
  
  strNewSheet = "NewSheet" ' <- Change to the new sheet name.
  
  Application.DisplayAlerts = False
  On Error Resume Next
  Sheets(strNewSheet).Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  
  With Ws
  
    Sheets.Add(, Sheets(Sheets.Count)).Name = strNewSheet
    
    .Range("A1").AutoFilter Field:=1, Criteria1:=Split(strMonths, ","), Operator:=xlFilterValues
    
    .AutoFilter.Range.EntireRow.Copy Range("A1")
  
    .AutoFilterMode = False
    
    Cells.EntireColumn.AutoFit
  
  End With
   
End Sub

 

[doriannjeshi]

Thank you all ,
@HighAndWilder excellent

I pressed your post as a solution , it accepts it but it used to show after. Now it doesn't show the solution on my end. I don't know if is a change or a bug

 

[Peter_SSs]

I pressed your post as a solution , it accepts it but it used to show after. Now it doesn't show the solution on my end. I don't know if is a change or a bug

It is possible that you accidentally clicked it a second time and the tick was removed. Have you tried to mark the post again?

 

[doriannjeshi]

It is possible that you accidentally clicked it a second time and the tick was removed. Have you tried to mark the post again?

It confirmed it this second time again. Hope it worked!