Copy rows based on column value on button click

[StacyC]

Hi everyone,

Long time lurker, first time poster - I'm not great with VBA to begin with and I'm at my wits end, so I'm hoping someone can help me.

I am trying to create a button on Sheet1 that, when pressed, will search for "X" in Column F of Sheet2 and copy the full contents of that row onto Sheet3. You following me so far?

I'm using this code, pilfered from the internet:

Private Sub Create_Click()

Application.ScreenUpdating = False


With Sheet2
    .AutoFilterMode = False
    With Range("F1", Range("F" & Rows.Count).End(xlUp))
        .AutoFilter 1, "X"
        On Error Resume Next
        .Offset(1).EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
    .AutoFilterMode = False
End With


Application.ScreenUpdating = True
Sheet3.Select


End Sub

When I click the button, I get the following error:

Run-time error '1004':
AutoFilter method of Range class failed

I've tried everything I can think of but I'm stumped. I'm sure it's probably something very obvious, but just not to me. Can anyone advise? Thanks!

​

 

[vcoolio]

Hello Stacy,

Your code works just fine for me. Are you sure that your headings are in Row1 with data starting in Row2?

Cheerio,
vcoolio.

 

[vcoolio]

.......or you could try and fully qualify sheet2 as the source sheet:-

Private Sub Create_Click()

Application.ScreenUpdating = False

    With Sheet2.Range("F1", Sheet2.Range("F" & Sheet2.Rows.Count).End(xlUp))
        .AutoFilter 1, "X"
        .Offset(1).EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter
    End With

Application.ScreenUpdating = True

Sheet3.Select

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[StacyC]

I can't believe it was that simple - yes, I had two rows of headers. Thank you!

 

[vcoolio]

You're welcome Stacy. I'm glad that I was able to help.
Thanks for the feed-back.

Cheerio,
vcoolio.