Autofilter macro not working correctly

honkin

Board Regular
Joined
Mar 20, 2012
Messages
184
Office Version
  1. 2016
Platform
  1. MacOS
hi

I need to have a vba macro to autofilter, then copy the results, without the header, to a different sheet. I have a number of them working in other worksheets, but this new sheet seems to throw up some issues for some reason.

Here is what I have

VBA Code:
Sub OO_Away_Lay_1()
'
' OO Away Lay v1 Macro
' This macro will filter for 1x2
'
    Dim ws As Worksheet, lc As Long, lr As Long

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    With ws.Range("A1", ws.Cells(lr, lc))
        .HorizontalAlignment = xlCenter
        Selection.AutoFilter
        .AutoFilter Field:=24, Criteria1:="Draw", Operator:=xlFilterValues
        If .Rows.Count - 1 > 0 Then
        On Error Resume Next
        .Columns("L:S").EntireColumn.Hidden = True
        .Columns("U:W").EntireColumn.Hidden = True
        .Columns("Y:CK").EntireColumn.Hidden = True
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        On Error GoTo 0
            Else
                Exit Sub
        End If
    End With
     
    Workbooks("Predictology_Trading Template v3.1.xlsm").Sheets("OO Away Lay v1") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Application.CutCopyMode = False
End Sub

I found it generated a RE 1004 error, "Method of range class failed"on the line Selection.AutoFilter. When I removed that line, the error message disappeared, but there are still some other issues.

You can see that there are various columns hidden from L onwards. When copying and pasting, only any visible cells up to L are copied. Anything visible after L is not copied.
The other issue is when there is nothing to copy. An RE message comes up on the destination workbook line. That workbook is not stored locally, but rather in a dropbox folder, yet I have the workbook open when running the macro.

Any thoughts on firstly why all visible cells are NOT being copied and, secondly, how to stop the error message when there is nothing to copy and paste?

Thanks in advance
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

honkin

Board Regular
Joined
Mar 20, 2012
Messages
184
Office Version
  1. 2016
Platform
  1. MacOS
Just to update, this is what I am talking about with regards to cells NOT being copied

Screen Shot 2564-01-11 at 11.17.40.png


Cells in columns A - K are copied OK, but from the first hidden column (L), no more cells are copied. I need it to copy from column A through to column AI, not counting the hidden columns or header

Any suggestions as to how to amend my above code to achieve either ALL visible cells except the header copied, or all cells in A through AI copied; again sans the header?

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,359
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top