Creating a Loop

M0J0jojo

New Member
Joined
Nov 27, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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:

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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I assume that this macro will go in "MainWorkbook.xlsm", that the range containing the filenames is in the active sheet and that you want the copied sheets to go into separate sheets with the sheet name starting with "Test" followed by a number. I'm not sure if the range in red would need to be modified.
Rich (BB code):
Sub FileSearch()
    Application.ScreenUpdating = False
    Dim FileName As Range, srcWB As Workbook, desWB As Workbook, srcWS As Worksheet, desWS As Worksheet, x As Long: x = 1
    Set desWB = ThisWorkbook
    Set srcWS = desWB.ActiveSheet
    For Each FileName In srcWS.Range("A1:A5")
        desWB.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Test" & x
        Set desWS = ActiveSheet
        Set srcWB = Workbooks.Open("C:\Project\Temp Folder\" & FileName)
        ActiveSheet.UsedRange.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp)
        desWS.ListObjects.Add(xlSrcRange, desWS.Range("$A$1:$D$6"), , xlYes).Name = "Test" & x
        x = x + 1
        srcWB.Close False
    Next FileName
    Sheets("Analysis").Select
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,771
Members
448,297
Latest member
cocolasticot50

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