Excel VBA filtering and copy pasting data

Question

Given a data set, that is lets say 10 columns. In column A i have date and in column B i have %. I want to filter for column A only 2014 data and for column B <70% and copy paste the filtered data into another worksheet. How do i go about writing the code for the next line to get to the first row of filtered data?

ActiveSheet.Range("$A$1:$AR$1617").AutoFilter Field:=5, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/28/2014")
ActiveSheet.Range("$A$1:$AR$1617").AutoFilter Field:=14, Criteria1:="<0.7" _
        , Operator:=xlAnd

Answer 1

ActiveSheet.Range("$A$1:$AR$1617").specialcells(xlcelltypevisible).copy sheets("Sheet2").range("A1")

Copies only the visible/filtered cells to a new worksheet as the first part of your question requests.

To get the first line of data, presuming there is data and you have headers in line 1, and paste that to a new sheet's next available row.

Activesheet.range("A1").end(xldown).entirerow.copy sheets("Sheet2").range("A65536").end(xlup).offset(1,0)

And a loop solution, that will copy data from sheet1 to sheet2, providing that column A always contains data (will be a bit slow on very large data sets):

Sub copyFilteredData(ByVal iYear As Integer, ByVal dPercent As Double)
Dim rCell As Range

For Each rCell In Sheet1.Range("A1:A65536").SpecialCells(xlCellTypeConstants)

    If Year(rCell.Offset(0, 4).Value) = iYear And rCell.Offset(0, 13).Value < dPercent Then
        rCell.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1, 0)
    End If

Next rCell

End Sub

Call using:

copyfilteredData 2014,0.7

Answer 2

Use Offset method like this:

ActiveSheet.Range("A1:AR1617").Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy 

Above code copies the filtered data excluding the header.
Is this what you're trying?