Run macro on all files in a specific folder
Hi Andreas,
Am Thu, 12 Dec 2013 23:01:47 -0800 (PST) schrieb AndreasHermle:
... so that it runs on all the excel-files in a folder (folder picker). There are only excel files in that specific folder.
try:
Sub Delete_NZ_From_AllSheets()
Dim objFSO As Object
Dim objOrdner As Object
Dim objDatei As Object
Dim lastrow As Long, r As Long
Dim wsh As Worksheet
'Modify for your Path
Const myPath = "F:\Test\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOrdner = objFSO.GetFolder(myPath)
On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each objDatei In objOrdner.Files
Workbooks.Open myPath & objDatei.Name
For Each wsh In ActiveWorkbook.Worksheets
With wsh
lastrow = .UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If UCase(.Cells(r, 5)) = "NZ" Then .Rows(r).Delete
Next r
End With
Next wsh
ActiveWorkbook.Close savechanges:=True
Next
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|