Use macro to loop through multiple worksheets to sort data and delete rows based on data

I am completely new to VBA and what I know I've learned from Google so please be patient and kind...I'm learning.

I have several workbooks (one per campus) with multiple worksheets (one per teacher) with data that needs to be sorted by column F (LEP) and then column D (Student Name). Rows in which column F is blank or equal to "LEP" needs to be deleted.

I have part of this figured out using:

Sub LoopAllSheetsInOpenWorkbooks()
Dim wb As Workbook
Dim sht As Worksheet

For Each wb In Application.Workbooks
    If wb.Name <> "PERSONAL.xlsb" Then
        For Each sht In wb.Worksheets
        
        ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("F2:F20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("D2:D20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.ActiveSheet.Sort
                .SetRange Range("A2:F20000")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            
        Next sht
    End If
Next wb
End Sub

______________________________________________________

It sorts for one worksheet and looks like it is for other worksheets in a workbook (page blinks really fast) but other worksheets are not sorted.

Also, I have no clue about deleting rows based on certain criteria. Any help would be greatly appreciated.

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(Since this is your first post I'll add them for you this time but please note for the future.

Your macro only ever handles the Active Sheet which never changes. You need to process each of the sheets you identify with the sheet variable.

So try instead

sht.Sort.SortFields.Add Key:  ..... etc.

And just another thought, but if you want to do any serious analysis with your data you should hold all your data on one sheet, not distributed across many sheets one for each teacher.
Add an extra column to a single sheet database and include the name of the teacher in the column and list all your data in a normalised table.

And if you ever need to analyse cross campus data you should go a step further and instead of many workbooks include them all in your single sheet database with an additional column to hold the campus name

Hi selixc

Welcome to the Forum!!!

Please add Code Tags around your Code...it's a Forum requirement.

This snippet of Code is always looking at the

ActiveWorkbook.ActiveSheet.

Replace all instances with

sht.

For example...

sht.Sort.SortFields.Add Key:=Range("F2:F20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
sht.Sort.SortFields.Add Key:=Range("D2:D20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sht.Sort

Hi selixc

Try this Code...

Option Explicit

Sub LoopAllSheetsInOpenWorkbooks()
   Dim wb           As Workbook
   Dim sht          As Worksheet

   Application.ScreenUpdating = False
   For Each wb In Application.Workbooks
      If wb.Name <> "PERSONAL.xlsb" Then
         For Each sht In wb.Worksheets
            With sht
               .Sort.SortFields.Add Key:=Range("F2:F20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               .Sort.SortFields.Add Key:=Range("D2:D20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               With .Sort
                  .SetRange Range("A2:F20000")
                  .Header = xlYes
                  .MatchCase = False
                  .Orientation = xlTopToBottom
                  .SortMethod = xlPinYin
                  .Apply
               End With
               If Not .AutoFilterMode Then
                  .Rows("1:1").AutoFilter
               End If

               .Range("A1:F20000").AutoFilter Field:=6, Criteria1:="=LEP", _
                                              Operator:=xlOr, Criteria2:="="
               .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
               .AutoFilterMode = False
            End With
         Next sht
      End If
   Next wb
   Application.ScreenUpdating = True
End Sub

Hi selixc

What version of Excel are you running? Your Profile shows 2003.

Hi selixc

I'm going to upload your Sample File with the Code embedded. The Code was developed in 2007 and performs as expected on my platform. Back at you in a few...

Hi selixc

The attached is your Sample File with the Code embedded. Open the Workbook...CTRL + x will fire the Code. Works on my Platform...what say you???

On the Sample File???

Originally Posted by selixc

It gave me another error.

Run-time error '1004': Cannot use that command on overlapping selections.

Debug highlights line: .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

Hi selixc

Honestly don't know...I get the attached after running the Code.

I'd suggest you upload the offending file...I'll see if I can duplicate the issue.