Repeatation of Macro in a Folder

[sg2209]

Good Morning All,

i have written a macro that performsa task very well now i want to run a loop in a folder, i have 50 sheets in a folder and i have to open each sheet and then run the code , then it saved the Worksheet in a folder to go to next sheet i need to open another sheet and carry on this task until i reaches last sheet .

how do i give a command to my macro to run by its own andi have this code saved in my personal macrobook.



Sub Prep_InsCr_Balance()

Dim MyFile As String
Dim Wbk As Workbook
Dim MyPath As String

MyFile = ActiveWorkbook.Name
Application.DisplayAlerts = False
' This Codes is Preapring the Unmannered Sheet in a Proper Format
Rows("1:2").EntireRow.Delete
Columns("A:A").Select
'Worksheets("NonClient Credit Balance").Range("A1:M1").AutoFilter Field:=1, Criteria1:="<>1"
ActiveSheet.Range("$A$1:$N$200000").AutoFilter Field:=1, Criteria1:="="
Worksheets("NonClient Credit Balance").Rows(3 & ":" & Worksheets("NonClient Credit Balance").Rows.Count).Delete
Worksheets("NonClient Credit Balance").AutoFilterMode = False
ActiveSheet.Range("$A$1:$N$200000").AutoFilter Field:=4, Criteria1:="=", Operator:=xlOr, Criteria2:="Patient Name"
Worksheets("NonClient Credit Balance").Rows(3 & ":" & Worksheets("NonClient Credit Balance").Rows.Count).Delete
Worksheets("NonClient Credit Balance").Rows(3 & ":" & Worksheets("NonClient Credit Balance").Rows.Count).Delete
Worksheets("NonClient Credit Balance").AutoFilterMode = False
Worksheets("NonClient Credit Balance").Columns("A:S").ColumnWidth = 9
Rows(1).EntireRow.Delete
Range("M:O").UnMerge
Worksheets("NonClient Credit Balance").Columns("N:O").EntireColumn.Delete
ActiveWorkbook.SaveAs Filename:="\\58.2.248.14\20007001_n01_nsl\Cash Application\Sachin\Sachin K Gupta\" & MyFile & Format(Now(), "DD-MM-YYYY")
ActiveWorkbook.Close
End Sub

Above Code is working fine only i need to run this by its own, please help me


this is what i tried to run a loop by puttting in the starting

MyPath = "Z:\GBL_CSH APP\Ins Credit Balance Reports\Daily Reports Credit Balances\Pulled In December" ' mapped his drive and All Sheets are in this Folder
MyFile = Dir(MyPath)
Do While Len(MyFile) > 0
If MyFile <> "Collated.xlsm" Then

Set Wbk = Workbooks.Open(MyPath & MyFile)
End If
MyFile = Dir
Loop

 

[davesexcel]

Once you have opened the workbook, it is now the active workbook.
You can call the macro each time

MyPath = "Z:\GBL_CSH APP\Ins Credit Balance Reports\Daily Reports Credit Balances\Pulled In December" ' mapped his drive and All Sheets are in this Folder
MyFile = Dir(MyPath)
   Do While Len(MyFile) > 0
         If MyFile <> "Collated.xlsm" Then
         
         Set Wbk = Workbooks.Open(MyPath & MyFile)

call Prep_InsCr_Balance

End If
MyFile = Dir
Loop

 

[sg2209]

Thank you so much for the response Dave.
Cannot i ammend this i to a existing code.
Active workbook is my personal workbook.
And i am not very much familiar with calling another macro.

 

[sg2209]

This is not working for me yet, tried with diffrent ways

 

[davesexcel]

I don't have a way to test the code as I don't have your Workbook.

I have a simple clip on how to loop through a folder of workbooks.
You will have to add your code that edits your workbooks.

 

[sg2209]

Thank You Dave for the time !!

Appreciate your efforts, i did it by watching the videos .