Copy data from multiple files and worksheets-names stored in excel columns

[NewToVBA1]

I am very new to excel VBA. I have a unique problem of copying data from multiple files.
Here it goes, I have a master file named "master.xlsm" and in that file I have a worksheet named "MyData". In that worksheet, I have column G that contains the file names upto row 20(last available data row). One example of a file name is Myfile1.xls. Each of these files have several worksheets, but the names of worksheets that needs to be opened (and data copied) are written in column H. So for example Myfile1.xls and its corresponding worksheet to be used (say "saving1") sits side by side in column G and H. All the files are in one folder.
I can write simple macros, but this seems diffcult to me. I need to copy a range of data from the relevant worksheets and consolidate it in a single worksheet in the master file. The data has to be copied below each sets of data from the worksheets in the order of column H.
As I am very new to VBA, I will appreciate, if the code has some comments that helps me understand it.
Thanks very much​

 

[Tony Miall]

Afternnon,

Maybe I'm missing something but why not just link the files to the master file?

 

[NewToVBA1]

Thanks Tony for the reply.
Actually, I need to automate this. I have around 75 files to copy data from and this code will help me in other similar work.

 

[AlphaFrog]

Sub Consolidate_Data()
    
    Dim wb As Workbook
    Dim ws As Worksheet, wsDest As Worksheet, wsFiles As Worksheet
    Dim strPath As String, strMsg As String, lMsgType As Long
    Dim cell As Range
    
    
    Set wsDest = ThisWorkbook.Sheets("Sheet2")   ' Destination worksheet
    Set wsFiles = ThisWorkbook.Sheets("MyData")  ' Worksheet with file list
    
    strPath = "C:\Temp\"    ' Folder of the data files
    
    Application.ScreenUpdating = False
    
    For Each cell In wsFiles.Range("G1", wsFiles.Range("G" & Rows.Count).End(xlUp))
        
        On Error Resume Next
            ' Attempt to open the next file
            Set wb = Application.Workbooks.Open(strPath & cell.Value)
        On Error GoTo 0
        
        If Not wb Is Nothing Then   'if the file was opened
            
            On Error Resume Next
                'Attempt to locate the sheet from column H
                Set ws = wb.Sheets(cell.Offset(, 1).Value)   'Define the source worksheet from column H
            On Error GoTo 0
            
            'If the sheet was successfully located
            If Not ws Is Nothing Then
                'Copy from source sheet Range A1:H10 to destination sheet next empty row.
                ws.Range("A1:H10").Copy Destination:=wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1)
            Else
                ' Log failure to locate worksheet to strMsg
                strMsg = strMsg & "Worksheet: " & cell.Offset(, 0).Value & vbLf
            End If
            
            wb.Close SaveChanges:=False
            Set ws = Nothing
        
        Else
            ' Log failure to open workbook to strMsg
            strMsg = strMsg & "File: " & cell.Value & vbLf
        End If
        
    Next cell
    
    Application.ScreenUpdating = True
    
    If strMsg = vbNullString Then
        strMsg = "All data from all files copied."
        lMsgType = vbInformation
    Else
        strMsg = "Couldn't locate the following..." & vbLf & vbLf & strMsg
        lMsgType = vbExclamation
    End If
    
    MsgBox strMsg, lMsgType, "Consolidation Complete"
    
End Sub

 

[NewToVBA1]

Thanks very much AlphaFrog, it was incredibly helpful and in time. Hats off !!!
Please suggest books or methods for me to achieve your level of programming.

 

[AlphaFrog]

You're welcome. I'm glad it was helpful.
I don't have any specific tutorial recommendations for Excel.