How to use an array to copy filter range into new worksheet?

[idnoidno]

19240I want to filter "January" and copy the corresponding value(B column) to the new worksheet,Any help will be appreciated19240

[mana]

Option Explicit

Sub test()

Rows(1).Insert

With Range("A1").CurrentRegion
.AutoFilter
.AutoFilter Field:=1, Criteria1:="january"
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Worksheets.Add
.Columns(2).Offset(1).Copy Range("A1")
End If
.AutoFilter
.Rows(1).Delete
End With

End Sub

[idnoidno]

Result:

A B
1 1
2 1
3 1

Below is I want .



A
B


january
1


january
1


january
1

[mana]

>　.Columns(2).Offset(1).Copy Range("A1")

　　.Offset(1).Copy Range("A1")

[mdmackillop]

Please don't quote whole posts, only those lined relevant to your question.

[mdmackillop]

Use the Macro recorder

'Recorded macro
Sub Macro14()
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:B").Select
Selection.AutoFilter
Range("E15").Select
Selection.AutoFilter
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$31").AutoFilter Field:=1, Criteria1:="january"
Columns("A:B").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("???1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End Sub

A little bit of editing



'Edited macro
Sub Macro15()
Dim Sh
Set Sh = ActiveSheet
With Sh
.Rows("1:1").Insert
.Columns("A:A").AutoFilter Field:=1, Criteria1:="january"
.Columns("A:B").Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
.Rows("1:1").Delete
End With
End Sub

[snb]

Sub M_snb()
Sheets.Add , Sheets(Sheets.Count)

With Sheets(1).Cells(1).CurrentRegion
.AutoFilter 1, "january"
.Copy Sheets(Sheets.Count).Cells(1)
.AutoFilter
End With
End Sub

[idnoidno]

.Offset(1).Copy Range("A1")


It is done.
But it is a little diffcult for me to understand.Would you plz to explain it?

[idnoidno]

This is the simplest CODE that gets the same result.

[mana]

You can use "F8 key" to execute the code line by line.

[idnoidno]

Sheets.Add , Sheets(Sheets.Count)

With Sheets(1).Cells(1).CurrentRegion
.AutoFilter 1, "january"
.Copy Sheets(Sheets.Count).Cells(1)
.AutoFilter
End With
End Sub


19244

If you get from the worksheet 1 worksheet 2, how to edit the original CODE ?

[mancubus]

using sheet name


Sub M_snb()
With Sheets("Sheet1").Cells(1).CurrentRegion
.AutoFilter 1, "january"
.Copy Sheets("Sheet2").Cells(1)
.AutoFilter
End With
End Sub


using sheet index


Sub M_snb()
With Sheets(1).Cells(1).CurrentRegion
.AutoFilter 1, "january"
.Copy Sheets(2).Cells(1)
.AutoFilter
End With
End Sub

[mdmackillop]

Hi Idnoidno
When you copy code, use "Paste without formatting" (Ctrl + Shift +V) to avoid the Font tags etc.

[mancubus]

modified:



Sub M_snb()
With Sheets("Sheet1").Cells(1).CurrentRegion
.AutoFilter 1, "january"
.Copy
Sheets("Sheet2").Cells(1).PasteSpecial xlPasteValues
.AutoFilter
End With
End Sub


using sheet index


Sub M_snb()
With Sheets(1).Cells(1).CurrentRegion
.AutoFilter 1, "january"
.Copy
Sheets(2).Cells(1).PasteSpecial xlPasteValues
.AutoFilter
End With
End Sub
[/QUOTE]