Looping through a range instead of using multiple calls

[rholdren]

I hope I can explain this correctly. I have a process that of moving data from a group of five files to five corresponding files in another directory. It works well but it is not very efficient.

I use a sub to call each one by one. It works but it just doesn't seem to be very efficient. Then I wondered "what if I had a lot of these files"? That would be a lot of code and the file could become huge. So then I though "What if I had the names of the file listed in column A and their corresponding file where the data needs to be moved to in column B? Below is the code I have used to move the data. I also included the range to help explain. I'm using 365.

Sub POST_ALL()
POST_FILE_1
POST_FILE_2
POST_FILE_3
POST_FILE_4
POST_FILE_5

End Sub

Private Sub POST_FILE_1()
'
' POST_FILE_1 Macro
' COPY DATA FROM TEST FILE 1 TO RECIVE FILE 1 SAVE AND CLOSE FILE
'
Application.ScreenUpdating = False
'
ChDir "H:\Personal\STOP LOSS\UMR Files\Original Report Data"
Workbooks.Open Filename:= _
"H:\Personal\STOP LOSS\UMR Files\Original Report Data\Sending Test File 1.xlsx"
Range("A2:M4").Select
Selection.Copy
ActiveWindow.Close
ChDir "H:\Personal\STOP LOSS\UMR Files\Reports To Be Sent"
Workbooks.Open Filename:= _
"H:\Personal\STOP LOSS\UMR Files\Reports To Be Sent\Receiving Test File 1.xlsx"
Sheets("Detail").Select
Range("A5").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True
Columns("B:B").EntireColumn.AutoFit
Sheets("Summary").Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Private Sub POST_FILE_2()
'
' POST_FILE_1 Macro
' COPY DATA FROM TEST FILE 2 TO RECIVE FILE 2 SAVE AND CLOSE FILE
'
Application.ScreenUpdating = False
'
ChDir "H:\Personal\STOP LOSS\UMR Files\Original Report Data"
Workbooks.Open Filename:= _
"H:\Personal\STOP LOSS\UMR Files\Original Report Data\Sending Test File 2.xlsx"
Range("A2:M4").Select
Selection.Copy
ActiveWindow.Close

(etc. etc. and so on )

in Sheet1

DATA TEST RECEIVING FILES	DATA TEST SENDING FILES
SENDING TEST FIEL 1	RECEIVING TEST FILE 1
SENDING TEST FIEL 2	RECEIVING TEST FILE 2
SENDING TEST FIEL 3	RECEIVING TEST FILE 3
SENDING TEST FIEL 4	RECEIVING TEST FILE 4
SENDING TEST FIEL 5	RECEIVING TEST FILE 5
SENDING TEST FIEL 6	RECEIVING TEST FILE 6
SENDING TEST FIEL 7	RECEIVING TEST FILE 7
SENDING TEST FIEL 8	RECEIVING TEST FILE 8
SENDING TEST FIEL 9	RECEIVING TEST FILE 9
SENDING TEST FIEL 10	RECEIVING TEST FILE 10
SENDING TEST FIEL 11	RECEIVING TEST FILE 11
SENDING TEST FIEL 12	RECEIVING TEST FILE 12
SENDING TEST FIEL 13	RECEIVING TEST FILE 13
SENDING TEST FIEL 14	RECEIVING TEST FILE 14
SENDING TEST FIEL 15	RECEIVING TEST FILE 15

 

[Joe4]

Here is a real simple example that shows you how you can loop through all the file names on Sheet1 and open them, etc.
This should give you the building blocks you need, and the logic for the rest should become evident, based on what is already there (I added documentation to explain what each step is doing):

Sub MyTest()

    Dim fpath1 As String
    Dim fpath2 As String
    Dim lr As Long
    Dim r As Long
    Dim file1 As String
    Dim file2 As String
   
    Application.ScreenUpdating = False
   
'   Designate the file paths to open and save files to
    fpath1 = "C:\Temp\Input\"
    fpath2 = "C:\Temp\Output\"
   
'   Assuming that file names exist in Sheet1 in columns A and B, find the last row in column A with data
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows on Sheet1, starting with row 2
    For r = 2 To lr
'       Capture names of files and store them as variables
        file1 = Sheets("Sheet1").Range("A" & r).Value
        file2 = Sheets("Sheet1").Range("B" & r).Value
'       Open file
        Workbooks.Open Filename:=fpath1 & file1 & ".xlsx"
'       rest of code below
   
    Next r
       
    Application.ScreenUpdating = True
       
End Sub

 

[rholdren]

I keep getting (I had to use drive H as they don't allow us to write to our C drive)

 

[Joe4]

Do you really have a file named "Input.xlsx" found at the very root of the "H" drive?

Note that my example was just a simple example.
You will need to change the file path details to suit/match your requirements.

 

[rholdren]

I have a folder on the H drive named input that contains the five files listed on the worksheet SENDING TEST FIEL 1 it has a corresponding file in a folder called output . I need it to copy the data A2 to M4 (of the worksheet named Detail and then paste it in the same location of a worksheet named detail of the corresponding worksheet in the output file

 

[Joe4]

If the folder name is supposed to "Input", then is sounds like you forgot to put the slash at the end of the line of code, i.e.

    fpath1 = "H:\Input\"

That also means that it appear that you did not set file1 equal to anything.
So however you edited this row:

        file1 = Sheets("Sheet1").Range("A" & r).Value

you don't appear to be picking up any values.

Verify the sheet name and column reference reflect your data structure.

 

[rholdren]

file1 = Sheets("Sheet1").Range("A2" & r).Value
file2 = Sheets("Detail").Range("A5" & r).Value

 

[Joe4]

Since we are looping through the rows, you need to pull off the row number from the hard-coded piece of the formula since we are adding it dynamically, i.e.

file1 = Sheets("Sheet1").Range("A" & r).Value

Also, if the list of names from for file1 and file2 come from different sheets, and have different starting points (row 2 and row 5), you may need to calculate each independently.
If that is the case, how are you ensuring that number of file names on "Sheet1" exactly matches the number of file names on "Details"?

You need to be very careful with VBA code! These minor details (this, and the slash at end of the file path) are very important, and can often make the difference between code working and code failing. So take careful note of how I wrote the code, and make sure not to overlook those details.

 

[rholdren]

admittedly I am confused. Would it be possible to pick this up on Monday? I apologize but I have to leave. I am being treated to an early birthday dinner.

thanks

 

[Joe4]

Sure, enjoy your birthday.

It would be helpful when you do get back to it, if you could include more detail about these two sheets, where the data resides, and their relationship to one another.