VBA Copying autofiltered range without field header

[mikezhel1987]

I want to copy the filtered data and paste it in difference excel worksheet. I am able to filter , copy and paste the rows. The only problem I am facing is that it is also copying and pasting the header every time. Please advise as to how to avoid header to be pasted. Below is the code that I used.

[/COLOR][COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;"> 
    Dim sh As Worksheet
    Dim ws As Worksheet
     
    Set sh = Sheets("June")
    Set ws = Sheets("July")
     
    sh.Range("t1", sh.Range("T" & Rows.Count).End(xlUp)).AutoFilter 1, "Yes"
    sh.Range("A2", sh.Range("S" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
    sh.[e2].AutoFilter
     </code>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

Appreciate any help from you guys. Thanks

 

[vcoolio]

HelloMike,

Change the "T1" in this line:-

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
    sh.Range("t1", sh.Range("T" & Rows.Count).End(xlUp)).AutoFilter 1, "Yes"</code>

to T2

Cheerio,
vcoolio.

 

[mikezhel1987]

Hi Cheerio,

I already did that but still no joy. the field header is still there.

 

[vcoolio]

Hello Mike,

Which row has your headings?

Cheerio,
vcoolio.

 

[mikezhel1987]

Hi Cheerio,

It is in row 1.

 

[MARK858]

What is the last visible row with data in column S when the filter has been applied?

and what happens with the code below?

Private Sub CommandButton1_Click()
    Dim sh As Worksheet
    Dim ws As Worksheet

    Set sh = Sheets("June")
    Set ws = Sheets("July")

    With sh.Range("A1", sh.Range("T" & Rows.Count).End(xlUp))
        .AutoFilter 20, "Yes"
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count - 1).SpecialCells(xlCellTypeVisible).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
    End With
    sh.Cells.AutoFilter
End Sub

 

[vcoolio]

Hello Mike,

Well, that is odd as the code as you have it is just fine.

So all the headings are in row 1 and the data set starts in row 2?

Try qualifying the ranges a little further:-

 sh.Range("T1", sh.Range("T" & [COLOR=#ff0000]sh[/COLOR].Rows.Count).End(xlUp)).AutoFilter 1, "Yes"
    sh.Range("A2", sh.Range("S" &[COLOR=#ff0000] sh[/COLOR].Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
    sh.[T1].AutoFilter

Let me know how that goes.

Cheerio,
vcoolio.

 

[vcoolio]

................and Mike,

If it still doesn't work, try the following:-

Private Sub CommandButton1_Click()

    Dim sh As Worksheet
    Dim ws As Worksheet
     
    Set sh = Sheets("June")
    Set ws = Sheets("July")
    
Application.ScreenUpdating = False

With sh
    .AutoFilterMode = False
    With Range("T1", Range("T" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Yes"
        Range(.Offset(1, -19), .Offset(1, -1)).Copy ws.Range("A" & Rows.Count).End(3)(2)
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.

 

[Leith Ross]

Crossposted here [h=2]​VBA Copy autofiltered range without header[/h]

 

[MARK858]

Crossposted here [h=2]​VBA Copy autofiltered range without header[/h]

That's me out