Loop Through Multiple Files And Call Macros (but Unable To Loop)

May 14, 2014

Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.

Why it stops after one file when using "Application.Run..." to call the macros?

NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.

Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long


View 4 Replies


Loop To Run Multiple Macros In Different Workbooks

May 23, 2006

I want to be able to run this many times looping through different file names. The only thing that changes between file names is the number.

Application.Run "'C:Documents and Settingspadi4545DesktopProjectRuns10.xls'!main"

View 3 Replies View Related

Loop Through Multiple Files To Create List Of Tab Names

Jul 26, 2013

I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.


Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False

[Code] .....

View 2 Replies View Related

VBA - Attach Multiple Files To Email Using Folder Path Loop?

Feb 7, 2014

I have two pieces of code that each work, but I am struggling to combine the two.

I started with Ron de Bruin's code to attach multiple files to an email and then found more code that will loop through a folder to attach multiple files to an email.

Essentially, I would like the structure of Ron de Bruin's code, with the ability to have folder paths in the cell range, rather than file names.

Here is the code that works to loop through a folder, but it only works when I have the paths listed in one column, not in a range (e.g. columns C-Z)

Sub Send_Indv_Files()
Dim OutApp As Object
Dim OutMail As Object


View 4 Replies View Related

Nested Loop Not Working; Pulls Data From Multiple Files Into One Table

Aug 8, 2006

I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.

Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................

View 5 Replies View Related

Call Statement In Loop Only Once

Oct 16, 2007

If ComboBox1.Text = " Market 1" Then
StoreNumList = Array ("66", "67", "107", "222") 'obviously array string is much longer than this
For x = LBound(StoreNumList) To UBound (StoreNumList)
StoreNum = StoreNumList(x)
ComboBox1.value = StoreNum
Call btnOK_Click_Process
Next x

Program runs fine first step through. It calls the btnOK_Click_Process sub routine just fine. However on second and subsequent For/Next cycles it does not call btnOK_Click_Process.

View 9 Replies View Related

Unable To Edit Macro To Loop Correctly

Oct 22, 2009

I need the macro I created to run (when assigned to a button) in specific sheets in the workbook, additionally if the workbook is saved under a different name by the user, the macro will continue to work. There are 10 sheets. I need it to run in sheets 6-10, when the button is clicked in sheet 5.
Then end goal in mind is to assign this macro to a button, so the user ,when ready ,can click the button to produce the results.

Background & Current Results :
My macro is simply an advance auto filter that copies the filtered data from sheet 3 to sheets 6-10 (non-unique values).
When applied per sheet , it works . When I modify the macro (I've tried several ways based on info published here) , the best result i can get is the data copies 6 times in the same page ( whatever active sheet is selected.). I have not assigned the modified macro to any buttons, why bother if I can't get it to run in the 1st place .

I suspect I need to redo the whole thing to have it simply copy the filtered data repeatedly to sheets 6-10 , but I am not that savvy to figure that out .

My Current Macro that Works Per Sheet
Sub tcktoriginal()
' tcktorginal Macro
' macro to run tickets
Range("cpt.code.table.").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:S3"), CopyToRange:=Range("A11:S11"), Unique:=False
End Sub

Examples That I have Tried and Didnít Work ........................

View 9 Replies View Related

Array With Button Names, Call Them One By One In A For Loop

May 12, 2009

I am having a problem calling buttons. I have an array with button names and another array with their caption texts and I would like to call them one by one in a for loop:

For i = 1 To UBound(button_array)
Sheets("Statistic").Shades(button_array(i)).Caption = button_text(i)
Next i

But the only way I seem to be able to call them is by their set names, like:

Sheets("Statistic").statistic.Caption = button_text_ENG(i)

View 2 Replies View Related

Loop Through Rows Until Blank And Call Macro

Feb 16, 2007

I have a spreadsheet with a list of names from cell A5 to A? - can be as many as 130 names or as few as 20. I have to create individual spreadsheets with the persons name as part of the filename. I have 3 subroutines which work manually ( CreateFile, LookUp and NewWorkbookSave ) so that I can generate individual files for those users who require them. However I need to be able on occasions to create a full teams worth of files with one click - to this end I need to be able to loop through cells A5 - A whatever and call the Subroutine CreateFile - I enclose the code I have so far

Const Fname = "AON Ver 3,0.xls" 'insert filename here like "AON Ver 3,0.xls"
Const DirName = "\Ukplscs105central repositoryAOMTally SheetsTeam upload Tally sheets"
Const TeamName = "AON" ' insert team Name here like "AON"
Const FilePath = DirName & TeamName
Dim count As Integer

Sub CreateFile()
ChDir FilePath
Workbooks.Open Filename:=FilePath & Fname
ActiveWindow.WindowState = xlMinimized
Call LookUp....................

View 5 Replies View Related

Endless Loop & Macros

Apr 27, 2009

I've used a loop in one of my macros and the loop goes on endlessly. I've been trying to come up witha way to overcome this problem but with no luck till now. knowing that I'm new to VBA i decided to turn to the big guns at mr.exxcel to help me out. Here's the code i have. Any alternatives to this would be VERY VERY VERY USEFUL!!!

Sub smalloops()
Dim i As Integer
Dim compliant As Integer
Dim noncompliant As Integer

compliant = 0
noncompliant = 0
For i = 1 To 60

Do Until Cells(i, 2) = ("RECOMMENDATIONS")

Do While Cells(i, 3) 0
If Cells(i, 7) = 1 Then
compliant = compliant + 1
ElseIf Cells(i, 7) = 2 Then.................

View 9 Replies View Related

Loop Within A Loop (repeat The Loop Values)

Mar 31, 2008

With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!

View 9 Replies View Related

Combining 2 Macros To Loop Properly

Nov 6, 2008

This code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).

And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.

View 6 Replies View Related

Unable To Open Multiple File And Run Macros In It

Dec 20, 2013

I am facing an issue where my macro is unable to open multiple files and run the macros in it. Below is the code that i am using and it stops at boss1.

Sub allboss()

Call boss1
Call boss2

End Sub

[Code] .....

View 2 Replies View Related

Loop Through Files In A Folder

Mar 11, 2014

I would like to create a Macro which does the following task. I have a Workbook Master.xlsx with a worksheet "source". The path of Master.xlsx is C:Test

In the Directory C:Testprojects i have about 50 files which all contain the String "Forecast" in their filename. These Forecast Workbooks need an update in the Worksheet "actuals"

So, i need a Macro that copies WS "source" from WB "Master" to WS actual in all WB's Forecast.

View 5 Replies View Related

Loop Through Files And A Run A Macro

Aug 4, 2009

I want to loop through all files in a directory and run a macro named "Main" in each file. The file that contain the loop-macro will stay in the same directory as the files I loop through.

View 4 Replies View Related

Loop Through Files On Share

May 30, 2008

I have the following code, and it is working well for one file. I'm trying to figure out how to do a loop to go through each region folder and update, save, and close each file just like the code below is doing for one file.

There are 10 region folders
Each region folder contains 8 or fewer files.

Please see the sample code below for the sample file location.

Sub TrendByRegion()

Application.EnableCancelKey = xlDisabled

Set wbOpen = Workbooks.Open(Filename:= _
"S:High Level ShareReportsReports by RegionPivot Table Files
Region 1Catalog File.xls")




End Sub

View 9 Replies View Related

Using Sendkeys And Loop To Download Files

Jul 7, 2012

I have created some code that directs me to a web page, and i use send keys to navigate my way to the input field. In this input field I am trying to put a cell value in a range use send keys to download the file, and the repeat the process for the next cell.

Here is my code so far, it does everything right up until pasting the copied cell into the input section of the web page - the issue is it is pasting nothing

Shell "C:Program FilesMozilla Firefoxfirefox.exe " & "https://website.com", vbMaximizedFocus
Application.Wait Now + TimeValue("00:00:05")
SendKeys "{TAB}"
' Input username

[Code] .....

View 9 Replies View Related

Loop Throough All Files In Folder

Apr 28, 2009

I did search but I couldn't find what I want. I have a load of .csv files in a folder. I want to loop through all files, run the macro below and close the .csv file without saving. My macro copied data onto another workbook and saves, but I don't know how to loop through all files within a folder.

I need some code that will open each file in the folder, run the macro below and close it again. I don't know how to put code tags in so I have put the code in a .txt.

View 3 Replies View Related

Macro Run As A Loop On All Files In A Given Folder

Mar 22, 2008

macro to run as a loop on all files in a given folder but allow for the option to Browse for the folder I want this to run in?

Sub CleanUp()
Dim i#, rng As Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False
For i = Worksheets.Count To 1 Step -1
If Worksheets.Count = 1 Then GoTo e
Set rng = Intersect(Worksheets(i).[11:11,13:13,23:23,25:25], _
With rng
.Replace What:=Chr(32), Replacement:="", LookAt:=xlPart
.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End With...........................

View 10 Replies View Related

Loop Through Files, Using Filename As String

May 8, 2008

I have some code that I use to convert files, and it works well, but I would like to make a couple of changes to it. If I run it now it requires user input for every file that is processed, using the input box. Instead of this I would like to use the filename minus the extension as this input.

The next step is selecting the file using the . Instead of this I would like the user to browse for the folder containing .csv files, and select the folder, then use the macro to loop through all files in the folder.

View 9 Replies View Related

Loop Through Files In Dated Folders

Nov 30, 2008

each folder is named by date, for example a folder name may be 04-01-2008. Within each folder there is one xls file named daily.xls.
(this never changes)
In date order from the folder name, I need to open each daily.xls run my code, then close daily.xls and then open the next one etc until there are no more.

example path is C:4-01-2008daily.xls

View 9 Replies View Related

Loop Through Folder, Run Macro On All Files

Mar 5, 2009

I am trying to process 60+ data files. I've recorded/written macros that do all of the processing, and now I would like to write a loop that will go through a folder with the data files (.txt, tab delimitted), and run the code on each file. I am using the Mac Version of Excel 2004. I have tried to adapt some code that I found in the forums, but after fixing a few errors, now nothing is happening when I run the code. The code is below. I have it in a module in the VBA editor.

length of the code; I'm assuming the problem is in the very beginning or end (the code I added for the loop), as the code in the middle that does the actual processing works.

Sub ProcessData()

Dim strDocPath As String
Dim strCurrentFile As String

strDocPath = "Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:"
strCurrentFile = Dir(strDocPath & MacID("TEXT"))

View 9 Replies View Related

Loop Through All Files In Subdirectories Of A Directory

Feb 26, 2010

I am having a bit of trouble with a bit of code. I have a bunch of code that I want to run on all excel files that live one level down from my main directory.

I have a general directory (orders) which contains a variable amount of subfolders each month (in picture below, 'Dan', 'Frank', 'Steve'), and I would like to look through all the excel files in 'Dan', 'Frank', 'Steve', but not in the main directory (orders), or any subdirectories within 'Dan', 'Frank', 'Steve'.

I am able to loop through all the excel files in the subdirectories individually if I hardcode the directory names, but as they are variable,

View 9 Replies View Related

CreateTextFile In Loop Not Creating Files

Dec 7, 2006

I have a problem with the code below. When i place the statement "MsgBox ("Done at " & Tester_array(N))", i am able to get the files i need. However, when i remove this statement, no files can be retrieved. Why is that so? Can somebody advise ?

Option Explicit
Sub File_Transfer()
Dim N As Integer
Dim fs As FileSystemObject
Dim A As TextStream
Dim Lotid As Variant
Dim Testerid As Variant
Dim dRetVal As Variant
Dim Tester_array(1 To 15) As String
Tester_array(1) = "aaa"
Tester_array(2) = "bbb"
Tester_array(3) = "ccc"
Tester_array(4) = "ddd".......................

View 2 Replies View Related

Loop Through Directories & Files With Dir Function

Jan 18, 2008

I am trying to do is loop through a series of directories under a target directory. When a directory is found, it looks within that directory and finds any .xls files, loops through those and copies/pastes them to the new target directory. I have managed to get it to work only on the first iteration of the loop through the directories, then it crashes. The error code is "Invalid Procedure Call or Argument." Here's the

If valFilePath = True Then
MsgBox (msg)
'Search for directories within source directory
strDir = Dir(ebsSource & "", vbDirectory)
'If a file has been found
Do While strDir <> ""
If strDir <> "." And strDir <> ".." Then
strFile = Dir(ebsSource & "" & strDir & "" & "*.xls")
Do While strFile <> ""
'Copy .xls files and paste in destination
copySource = ebsSource & "" & strDir & "" & strFile
copyDestination = ebsDestination & "" & strDir & "" & strFile
FileCopy copySource, copyDestination
strFile = Dir
End If
'Crashes here after looping through the first iteration of Excel files
strDir = Dir

View 2 Replies View Related

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.

View 4 Replies View Related

Loop Through Cells And Ranges Reverse Order With Backwards Loop

Aug 30, 2006

I am looping through each cell in a range and I would like to loop in reverse order.

Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
'Code here to delete a row based on criteria

I have tried:

For Each CELL In Range("C" & TotalRows, "C1")

and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.

View 7 Replies View Related

Copy Cells In Loop Based On Loop Increment Being Multiplied

Feb 7, 2008

I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View 3 Replies View Related

Loop Through All Files Rename And Save In Another Folder

Feb 27, 2013

I have excel files in a folder, I need a macro to loop through all files, rename them and save them in another folder. the file's name is available in a cell in each file, I am able to get the file name as text (I had to dig a little to get the text as it was between ""). Well I am stuck right now because the macro is doing what it's supposed to do for one excel file only and does not continue with the rest of the available files for a reason.

Sub RenameAllFiles()
Dim Bk As Variant
Dim n As Integer
Dim NewName As String

[Code] .....

View 8 Replies View Related

Excel 2011 :: Loop Through All CSV Files In A Folder

Nov 23, 2011

I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...

Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string


For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.

Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved