Hi,
I have a question about looping. I had made a macro which looped through all of the files in a specific folder, opened the file ran some tasks closed the file and moved on. However I need to change the loop now so that rather than looping through all of the files in the folder it loops through a range of file names on the Work Book, performs an IF, Then, Else and then loops to the next file on the list.
Say for example A1:A5 contains "Test File 1.xlsx" through "Test File 5.xlsx". I have a macro set up to search a folder in a given directory for the file with the name in the active cell, if it's not there do nothing but if it is there open the file, copy its contents and paste to a sheet in the main Work Book.
The VBA I'm using to perform this is:
What I want to happen next is for the Macro to Loop to the next file name ie. A2 "Test File 2.xlsx" and repeat the procedure until it is done for all of the range A1:A5.
Any help would be hugely appreciated.
I have a question about looping. I had made a macro which looped through all of the files in a specific folder, opened the file ran some tasks closed the file and moved on. However I need to change the loop now so that rather than looping through all of the files in the folder it loops through a range of file names on the Work Book, performs an IF, Then, Else and then loops to the next file on the list.
Say for example A1:A5 contains "Test File 1.xlsx" through "Test File 5.xlsx". I have a macro set up to search a folder in a given directory for the file with the name in the active cell, if it's not there do nothing but if it is there open the file, copy its contents and paste to a sheet in the main Work Book.
The VBA I'm using to perform this is:
VBA Code:
Sub FileSearch()
Dim FileName As String
FileName = VBA.FileSystem.Dir("C:\Project\Temp Folder\" & ActiveCell.Value)
If FileName = VBA.Constants.vbNullString Then _
Else
Workbooks.Open "C:\Project\Temp Folder\" & ActiveCell.Value
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("MainWorkbook.xlsm").Activate
Sheets("Test 1").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$6"), , xlYes).Name = _
"Test1"
Sheets("Analysis").Select
Windows(ActiveCell.Value).Activate
ActiveWindow.Close
End If
End Sub
What I want to happen next is for the Macro to Loop to the next file name ie. A2 "Test File 2.xlsx" and repeat the procedure until it is done for all of the range A1:A5.
Any help would be hugely appreciated.