Run Time Error For Copying Rows Based on Cell Value Using Filter

[Shaun WKC]

Good day,

I would appreciate any input in solving my problem. I have put together a simple calculator providing outputs in selected sheets. The Data goes beyond 6000 rows at times. I have been searching to solve this problem for the past 4 hours with different variation, but I like the idea of using filters, as this code will be repeated multiple times for different scenarios.

I have a button that needs to copy rows based on the value in cell AU. The cell value is "<0.29" that I am trying to filter by on a sheet named Calculations. The pasted row needs to populate the first available row in the sheet named Acceptable. I Keep getting a runtime error based on my code below. Also, is there a way to prevent duplication on this sheet and I would like to to skip values that are errors.

Thank you in advance.

Private Sub cb029_Click()

Worksheets("Calculations").Activate
 
    lastrow = Cells(Rows.Count, 47).End(xlUp).Row
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
     
    Application.ScreenUpdating = False
     
    With ActiveSheet
        .AutoFilterMode = False
        .Range("A1", .Cells(lastrow, lastcol)).AutoFilter field:=47, Criteria1:="<0.29"
        .Range("A1", .Cells(lastrow, lastcol)).SpecialCells(xlCellTypeVisible).Copy Acceptable.Range("A1")
        .AutoFilterMode = False
    End With
     
    Application.ScreenUpdating = True
    
Unload Me


Worksheets("Acceptable").Activate
    
End Sub

 

[Shaun WKC]

Bmp

 

[Shaun WKC]

Has anyone possibly got a different way around it then? Sorry for the multiple bumps, even just a reference to a similar code would be great.

 

[Firefly2012]

Hi

Try amending your autofilter section to:

With ActiveSheet
        .AutoFilterMode = False
        .Range("A1", .Cells(lastrow, lastcol)).AutoFilter field:=47, Criteria1:="<0.29"
        .Autofilter.Range.Copy 
        Acceptable.Range("A1").PasteSpecial
        .AutoFilterMode = False
    End With

I suspect it was the second Autofilter line that gave you the error, but you didn't state which was the problem in the original post so the above may not fix anything.

 

[Shaun WKC]

Hi Firefly, thank you for your time. The error seems to come up in the following line:

 .AutoFilter.Range.Copy Acceptable.Range("A1").PasteSpecial

I am not sure why, it is successfully filtering and copying though, so thank you for that.

 

[Shaun WKC]

Sorry for the next post, solved the problem. Thank you for your input.

  With ActiveSheet        .AutoFilterMode = False
        .Range("A1", .Cells(lastrow, lastcol)).AutoFilter field:=47, Criteria1:="A"
        .AutoFilter.Range.Copy Destination:=Sheet7.Range("A1") 'use sheet reference not name
        .AutoFilterMode = False
    End With