Running same macro for multiple files

rickbossman

New Member
Joined
May 13, 2021
Messages
1
Hey everyone,

I'm trying to run this code within around 60 files. The files all have the same format so this same macro can be run in all of them. I've tried using some code I found online which should run between multiple files but I'm lost. I'm a newbie when it comes to vba unfortunately.
Macro:
VBA Code:
Dim rng As Range
Dim rng_del As Range

Set rng = Range("A1").CurrentRegion

'Remove NC from sheet
rng.AutoFilter field:=1, Criteria1:="NC"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

rng.AutoFilter field:=10, Criteria1:=">1"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

rng.AutoFilter field:=8, Criteria1:=">=28"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

rng.AutoFilter field:=9, Criteria1:=">=28"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

Then I combined with the code from another person:
VBA Code:
Sub ProcessFilesInFolder()
    Dim FolderName As String, filepathname As String, NextFile As String
    Dim wbname As String, dsName As String
    
    FolderName = "C:\Users\mydocuments\Documents\mako"  'Change this to your directory
    NextFile = Dir(FolderName & "*.csv", vbNormal) 'change the extension if needed
    
    While NextFile <> ""
    
        filepathname = FolderName & NextFile
        Workbooks.Open Filename:=filepathname
            
        wbname = ActiveWorkbook.Name  'use WBName to refer to the name of the current workbook
        dsName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
                                  'that was created when you opened the text file


Dim rng As Range
Dim rng_del As Range

Set rng = Range("A1").CurrentRegion

'Remove NC from sheet
rng.AutoFilter field:=1, Criteria1:="NC"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

rng.AutoFilter field:=10, Criteria1:=">1"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

rng.AutoFilter field:=8, Criteria1:=">=28"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

rng.AutoFilter field:=9, Criteria1:=">=28"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

      
        MsgBox (wbname)   'this is just for testing...remove it when you know you're getting the files you want

        Workbooks(wbname).Close Savechanges:=True
        
        NextFile = Dir()   'this gets the name of the next file
        
    Wend



End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,770
Office Version
  1. 2016
Platform
  1. Windows
If your code portion is working then try this looping. Should be easy to understand
VBA Code:
Sub LoopAllFilesInAFolder()

'Loop through all files in a folder
Dim FPath As String
Dim FName As Variant
Dim ws As Worksheet
Dim wb As Workbook

FPath = "C:\Users\mydocuments\Documents\mako\"        ' Set your folder path here
FName = Dir(FPath)

While FName <> ""
    Set wb = Workbooks.Open(fileName:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    For Each ws In wb.Sheets
        
        Dim rng As Range
        Dim rng_del As Range

        Set rng = ws.Range("A1").CurrentRegion

        'Remove NC from sheet
        rng.AutoFilter field:=1, Criteria1:="NC"
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.AutoFilterMode = False

        rng.AutoFilter field:=10, Criteria1:=">1"
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.AutoFilterMode = False

        rng.AutoFilter field:=8, Criteria1:=">=28"
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.AutoFilterMode = False

        rng.AutoFilter field:=9, Criteria1:=">=28"
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.AutoFilterMode = False
        
    Next
    'Close wb without saving
    wb.Close False
    'Set the fileName to the next file
    FName = Dir
Wend

End Sub
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,770
Office Version
  1. 2016
Platform
  1. Windows
My bad. I was thinking of you just reading files. You need to save the file after run.

So, you need to open file not as ReadOnly. It should be
Rich (BB code):
ReadOnly:=False

Before closing workbook, you need to save the file. Add
wb,Saved=True
 

Forum statistics

Threads
1,144,340
Messages
5,723,804
Members
422,518
Latest member
quack_quack

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