Macro to load 10 files from different folder into different sheets in Master File

[joxyin]

Good afternoon,
I'm seeking help in completing my macro to load 10 files into different sheets in master workbook.

In Master Workbook, I have "Main" sheet which has a list of reports with file paths (that changes every month), and 10 sheets for each report to load onto.
I'm able to load individually report by macro onto specified sheet, but when I have 10 or more reports, I believe there is an easier way to do a "LOOP", instead of copying the same code 10 times.
Ideally, I only need to change the file path in MAIN sheet every month/fortnight and the macro will load from the specified folders to each sheet onto master workbook.

My current code is as below, I hope it would be readable when I submit the thread. I also attached a picture of the code just in case.

============================================================

Sub Load_Reports()
'
' Load_Reports Macro
'
Set thisbook = ActiveWorkbook

Dim File1 As String, File2 As String, File3 As String, File4 As String, File5 As String, File6 As String, File7 As String, File8 As String, File9 As String, File10 As String


File1 = Sheets("MAIN").Range("PAY").Value
File2 = Sheets("MAIN").Range("SOP").Value
File3 = Sheets("MAIN").Range("SOH").Value
File4 = Sheets("MAIN").Range("SOE").Value
File5 = Sheets("MAIN").Range("SEC").Value
File6 = Sheets("MAIN").Range("AAD").Value
File7 = Sheets("MAIN").Range("CSR").Value
File8 = Sheets("MAIN").Range("LSR").Value
File9 = Sheets("MAIN").Range("EAR").Value
File10 = Sheets("MAIN").Range("HER").Value

'please help setup loop to open/load/copy & paste/close all 10 files


Workbooks.Open Filename:=File1

Columns("A:Z").Select
Selection.Copy

thisbook.Activate

Sheets("PAY").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select

Workbooks.Open Filename:=File1
Application.CutCopyMode = False
ActiveWindow.Close

' I used to have only 3 files to load so I copy the above code and change file1 to file2/file 3.

End Sub

================================================================
Appreciate any help I could get.
Thank you. Jo

 

[John_w]

Try this macro. I've added these enhancements:

• Add each sheet if it doesn't exist
• Warning if the .xls file doesn't exist, with the option to continue or quit the macro.

Note - it doesn't use named ranges like your code, but simply loops through column B starting at B2.

Option Explicit

Public Sub Load_All_Reports()

    Dim r As Long
    Dim destWs As Worksheet
    Dim reportWb As Workbook
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Worksheets("Main")
        For r = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            If Dir(.Cells(r, "C").Value) <> vbNullString Then
                Set destWs = Add_Sheet(.Cells(r, "B").Value)
                Set reportWb = Workbooks.Open(.Cells(r, "C").Value)
                reportWb.Worksheets(1).UsedRange.Copy
                destWs.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False
                reportWb.Close False
            Else
                .Activate
                If MsgBox("Cell: " & .Cells(r, "C").Address(False, False) & vbCrLf & "File: " & .Cells(r, "C").Value & vbCrLf & vbCrLf & "Click OK to continue loading reports or Cancel to quit", vbOKCancel + vbExclamation, "Report file not found") = vbCancel Then
                    MsgBox "Report loading aborted", vbInformation
                    Exit Sub
                End If
            End If
        Next
    End With
    
    Application.ScreenUpdating = True
    
End Sub


Private Function Add_Sheet(sheetName As String) As Worksheet

    With ThisWorkbook
        Set Add_Sheet = Nothing
        On Error Resume Next
        Set Add_Sheet = .Worksheets(sheetName)
        On Error GoTo 0
        If Add_Sheet Is Nothing Then
            Set Add_Sheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            Add_Sheet.Name = sheetName
        End If
    End With
    
End Function

 

[joxyin]

Hi John,
Thank you so much for your reply. I have just tested it and it worked.
Your code is way above my knowledge level of VBA, I can only copy your code for now, but it's a very good chance for me to learn more advanced skills.
Thanks again!
Regards, Jo