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:
Then I combined with the code from another person:
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