run macro on all files

S

Suryaprakash

New Member
Joined
Aug 1, 2011
Messages
41
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Code: 
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
 
Upvote 0
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
 
Upvote 0
Then you could put the name of the macro in here:

Rich (BB code): 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VB search folder for older version of a file and move it to archive
Replies
7
Views
238
Davefromleeds
D
Y
  • Question Question
A macro to change formatting of 27 graphs
Replies
2
Views
80
yabi100
Y
H
  • Question Question
selecting a folder and copying all files into another folder
2 3
Replies
20
Views
503
Holley
H
T
  • Solved
Open all files in a folder and run macro
Replies
3
Views
677
Trebor76
T
D
  • Question Question
VBA Download URL and rename file
Replies
6
Views
358
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,224,504
Messages
6,179,142
Members
452,892
Latest member
JUSTOUTOFMYREACH

Share this page

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
Back
Top