Applying master workbook VBA to 1000 files

Question

Following the instructions in this Stack Overflow question, I tried to run my macro with the following code:

Sub ay1()
Dim fileName, Pathname As String
Dim wb As Workbook
Pathname = "/Users/ayy/Downloads/Folder1/STATS1/"
fileName = Dir(Pathname & "*.csv")
Do While fileName <> ""
    Set wb = Workbooks.Open(Pathname & fileName)
    DoWork wb
    wb.Close SaveChanges:=True
    fileName = Dir()
Loop
End Sub

Sub DoWork(wb As Workbook)

With wb
    
    Selection.AutoFilter
            ActiveSheet.Range("$A$1:$C$191").AutoFilter Field:=3, Criteria1:="="
            Range("C2:C190").Select
            Selection.EntireRow.Delete
            ActiveSheet.Range("$A$1:$C$96").AutoFilter Field:=3
            Range("E95").Select
            ActiveWorkbook.Save
            ActiveWindow.Close

    End With
End Sub

I saved this in a "master workbook" that is macro-enabled in the same directory where all my .csv files are located. I clicked run macro and selected ay1.

This is not running on any of my files. I'm not getting any errors.

Are you missing the drive in your pathname? Or are you maybe on a Mac? Also - the `With` block in `DoWork` isn't doing anything. — Tim Williams, Mar 19 '20 at 23:13
Yes, I'm using a Mac. I'm a little confused, why is the With block in DoWork not doing anything? @TimWilliams — hsayya, Mar 19 '20 at 23:28
When you use `With` you use a leading `.` to tie references inside the `With` block to the object which is the subject of the `With` statement. Since your're on a Mac - you should note that wildcards are not supported with `Dir()` - you need to loop over all the files and check their names to find the ones you want. **EDIT** - I guess it does work now on Office 2016+ — Tim Williams, Mar 19 '20 at 23:34
I am working on an Office 2016+, so if that's the case and Dir isn't an issue.. what might be the issue do you think? — hsayya, Mar 19 '20 at 23:39

score 0 · Answer 1 · answered Mar 19 '20 at 23:40

Using a With block: you need to tie your references to wb with a leading .

Sub DoWork(wb As Workbook)

    With wb.Sheets(1)
        .UsedRange.AutoFilter
        .Range("$A$1:$C$191").AutoFilter Field:=3, Criteria1:="="
        .Range("C2:C190").EntireRow.Delete
        .Range("$A$1:$C$96").AutoFilter Field:=3
    End With
    wb.Save
    wb.Close
End Sub

Applying master workbook VBA to 1000 files

1 Answers1