VBA code stuck (extract data from worksheet)

[chingg1011]

Hi Expert,

VBA purpose: daily run data from an excel worksheet to extract top 20 loan data into new worksheet for accumulative base.
Problem encountered: below code was stuck from error code 91 in yellow line, but can be successful in another user's note book.
Help; can any expert help to solve out below problem for running smooth this vba task ?

'Dennis Mak, last update on May 2024


Sub Extract_DailyLoan()
'Excel VBA code to loop through files in a folder with Excel VBA




Dim MyFolder As String, MyFile As String, workbookname As String


'Opens a file dialog box for user to select a folder
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
MyFolder = .SelectedItems(1)
Err.Clear
End With

'stops screen updating, calculations, events, and statsu bar updates to help code run faster
'you'll be opening and closing many files so this will prevent your screen from displaying that

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'This section will loop through and open each file in the folder you selected
'and then close that file before opening the next file

MyFile = Dir(MyFolder & "\", vbReadOnly)

Do While MyFile <> ""
DoEvents
On Error GoTo 0
Set OtherWorkbook = Workbooks.Open(MyFolder & "\" & MyFile, UpdateLinks:=0, ReadOnly:=1)
OtherWorkbook.worksheets("sheet1").Activate
OtherWorkbook.worksheets("sheet1").Select


'start doing the main job of copying data from daily loan table file
Range("A1:AX12909").Select
Range("A2").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.End(xlUp).Select
Columns("Z:Z").Select
ActiveWorkbook.worksheets("sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.worksheets("sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("Z1:Z12909"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal

With ActiveWorkbook.worksheets("sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.End(xlToLeft).Select
Range("A2:AX21").Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("Loan table top 20").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues



Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir

Loop

'turns settings back on that you turned off before looping folders

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Alex Blakenburg]

Your code needs a bit of a rewrite / cleanup but for a quick fix try replacing the first:

Selection.AutoFilter

with this

If Sheet1.AutoFilterMode = False Then Selection.AutoFilter