CODEWITHSUNDEEP
excelvba
James
James

Reputation: 9

Copy a specific range when Autofilter is applied

Need some help here. Row A is used for headers and has a "Autofilter" applied for Field 16, in this case when filtering it to find "Q1". I will be repeating this step for other quarters.

I am trying to only copy a specific row and range at a time. So for example: first Column A...copy what has only been filtered then paste where i desire...then I would a write new line to get only Column D and paste that where ever i desire.

I am using UsedRange at the moment but unsure how I would transition out of it. Would Range(A:A) work and Range("B:B") then if it was consecutive can I do Range(F:H) for columns F,G and G=H. However keep getting Error 1004

Hope I have made sense

Dim a As Workbook
Dim b As Worksheet
Dim c As Worksheet
Dim d As Worksheet

Set a = ThisWorkbook
Set b = Worksheets("Opportunity(BE)")
Set c = Worksheets("Pipeline(BE)")
Set d = Worksheets("Renewal(BE)")

a.Worksheets("Probable").Range("A1:T1").AutoFilter Field:=17, 
Criteria1:="Open"

a.Worksheets("Probable").Range("A1:T1").AutoFilter Field:=16, Criteria1:="Q1"
a.Worksheets("Probable").Range("B:B").Offset(1).SpecialCells(xlCellTypeVisible).Copy
b.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Upvotes: 0

Views: 221

Answers (1)

SJR
SJR

Reputation: 23081

Untested, but one way is to use Intersect to capture the specific column you want.

Sub x()

Dim a As Workbook
Dim b As Worksheet
Dim c As Worksheet
Dim d As Worksheet
Dim r As Range

Set a = ThisWorkbook
Set b = Worksheets("Opportunity(BE)")
Set c = Worksheets("Pipeline(BE)")
Set d = Worksheets("Renewal(BE)")

With a.Worksheets("Probable")
    .Range("A1:T1").AutoFilter Field:=16, Criteria1:="Open"
    Set r = Intersect(.Range("B:B"), .AutoFilter.Range) 'column B of filtered data
    r.Offset(1).Resize(r.Rows.Count - 1).Copy 'remove header
    b.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With

End Sub

Upvotes: 1

Related Questions