Using AUTOFILTER to copy filtered data to the bottom of a list (different Excel Sheet) without Header Row

Question

I am using AUTOFILTER to filter data on one Excel worksheet (Input_Wkr_Hrs) and append the data to the bottom of a list (table) on another worksheet (Output) in the same workbook. The problem I have is that when the data gets added to the bottom of the list, it includes the HEADER ROW Names. How do you copy filtered data using AUTOFILTER and not include the HEADER NAMES when appending to the bottom of a list?

The source table on the worksheet (Input_Wkr_Hrs) has three columns (Emp Name, Section, Hours).

I am in the beginning stages of learning Excel VBA; this is my first question on Stackoverflow. Code below

Sub GetWorkerData() ' shImput_Wkr_Hrs name if source data sheet ' shOutout name of destination sheet Dim rg As Range Set rg = shImput_Wkr_Hrs.Range("C15").CurrentRegion ' 'TurnOffFunctionality ' Turn off Screen updating and other stuff

With rg
    .AutoFilter field:=3, Criteria1:=">0"    ' filter working hrs hours >0
    .SpecialCells(xlCellTypeVisible).Copy    'Destination:=shOutput.Range("a1")
    '
    '.Offset(1).Resize(Selection.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
    shOutput.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues

' rg.AutoFilter End With ' shOutput.Activate 'TurnOnFunctionality ' Turn on Screen updating and other stuff End Sub

I screwed up pasting the code into my question... sorry about that. — Knightflier

RuanRuan RuanRuan · Accepted Answer · 2019-12-09T09:51:34

Assume that ws_Data contains the data that you would like to filter/copy and ws_Output is the worksheet where the data will be pasted.

Dim rng_Visible as Range, outputLastRow as long
' Assume that the first line is the header
With ws_Data
    .Range("A1").AutoFilter Field:=3, Criteria1:=">0"

    ' Check if there is data to copy
    If .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
        Set rng_Visible = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        ' Copy the visible filtered range
        rng_Visible.Copy

        ' Find the last row in Output worksheet using column A, make sure the column you use does not have any blank value in middle
         outputLastRow= ws_Output.Cells(ws_Output.Rows.Count, "A").End(xlUp).Row

         ' Paste to the output worksheet starting from column A
         ws_Output.Range("A" & outputLastRow).PasteSpecial xlPasteAll
         Application.CutCopyMode = False

         ' Turn off the auto filter 
         ws_Data.AutoFilterMode = False
    End If

End With

Using AUTOFILTER to copy filtered data to the bottom of a list (different Excel Sheet) without Header Row

1 Answers