run macro on all files

[Suryaprakash]

Normally when we record a macro in excel, it is applicable on one file. As such, we are required to run the macro in every file in the folder separately, which is very time consuming. I have 250 to 300 files in a folder with similar structure and I am required to perform various operations on the data, which consumes a lot of time. I would highly appreciate if somebody can tell me how to loop all the files in the folder to perform certain operation on all files (without opening all the files together). We will take a specific example as follows.
Folder path: C:\Folder1\ (contains 250 files of 1 sheet each in Excel 2000)
Opeation to perform: Range("B10:D35").Delete
How can this be done on all files?
I have recorded 4-5 different macros including sorting, conditional formating etc. Can I run any of them by simply putting the name of the macro and if yes, where exactly should the macro name be placed in the code? Putting the whole macro is not desirable as recorded macros are very very lengthy, hence the macro name.
Any help in this respect would be highly appreciated.

 

[wigi]

Sub LoopThroughFiles()
    
    FolderName = "C:\Folder1\"
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    Fname = Dir(FolderName & "*.xls")

    'loop through the files
    Do While Len(Fname)

        With Workbooks.Open(FolderName & Fname)

           ' here comes the code for the operations on every file the code finds

        End With

        ' go to the next file in the folder
        Fname = Dir

    Loop
    
End Sub

 

[wigi]

Why are you asking this a second time?

You were already given this kind of code:
see http://www.mrexcel.com/forum/showthread.php?t=568392

 

[Suryaprakash]

Hi Wigi

Thanks for the code Loop through Files. This is of great use to me. Supposing I have to run a macro (without copying the whole lengthy code, just inserting the macro name) within this code, how can I do it? What is the syntax for the same?

Thanks once again for your help.

Regards
Suryaprakash

 

[wigi]

Then you could put the name of the macro in here:

Sub LoopThroughFiles()
    
    FolderName = "C:\Folder1\"
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    Fname = Dir(FolderName & "*.xls")

    'loop through the files
    Do While Len(Fname)

        With Workbooks.Open(FolderName & Fname)

           ' here comes the code for the operations on every file the code finds

           'HERE if you want to execute the second macro for every file in the loop
           NameOfTheMacro

        End With

        ' go to the next file in the folder
        Fname = Dir

    Loop
    
End Sub

 

[TonyBeo]

I a trying to get this macro to work for my problem. I need to copy cells from one workbook and paste them into multiple workbooks. The macro I am trying to use is below. The code fails when I try to activate the sheet to paste the copied cells to (see Workbooks.Activate (FolderName & Fname) below).

Sub LoopThroughFiles()

FolderName = "S:\CRT-General Equipment Forecast Lists\2015 Forecast Information\2015 Country Templates\11-26-14 backup"
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
Fname = Dir(FolderName & "*.xls")

'loop through the files
Do While Len(Fname)
With Workbooks.Open(FolderName & Fname)
' here comes the code for the operations on every file the code finds
'HERE if you want to execute the second macro for every file in the loop
Copy_discounted_costs_to_country_forecasts
End With
' go to the next file in the folder
Fname = Dir
Loop

End Sub
Sub Copy_discounted_costs_to_country_forecasts()
'
' Copy_discounted_costs_to_country_forecasts Macro
'
'
Windows("2015 Shortage Reconcile w Revised Discounted Costs.xlsm").Activate
Range("L1").Select
ActiveWindow.ScrollRow = 326
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 302
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 3
Range("L5:L1669").Select
Selection.Copy
Workbooks.Activate (FolderName & Fname)
Fname Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub