'Source: www.wisdombydata.com

Sub ImportData()
   Dim textFile As Workbook
   Dim OpenFiles() As Variant
   Dim i As Integer
   
    'Prompt User for Folder containing the files
    OpenFiles = Application.GetOpenFilename(Title:="Select the folder that contains the completed Risk Assessment Tools", MultiSelect:=True)
    
     
     For i = 1 To Application.CountA(OpenFiles)
            Set textFile = Workbooks.Open(OpenFiles(i))
         
         Application.ScreenUpdating = False
         Application.DisplayAlerts = False
                'The "Order Date (MMM/DD/YYYY)" Field
                If IsEmpty(textFile.Sheets("Registration Form").Range("D4").Value) = True Then
                textFile.Sheets("Registration Form").Range("D4").Value = "N/A"
                End If
                
                textFile.Sheets("Registration Form").Range("D4:F4").UnMerge
                textFile.Sheets("Registration Form").Range("D4").Copy
                Workbooks(1).Activate
                ActiveSheet.Select
                
                Range("A1000").Select
                Selection.End(xlUp).Select
                
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
    
                'The "First Name" Field
                If IsEmpty(textFile.Sheets("Registration Form").Range("D7").Value) = True Then
                textFile.Sheets("Registration Form").Range("D7").Value = "N/A"
                End If
                
                textFile.Sheets("Registration Form").Range("D7:F7").UnMerge
                textFile.Sheets("Registration Form").Range("D7").Copy
                Workbooks(1).Activate
                ActiveSheet.Select
                
                Range("B1000").Select
                Selection.End(xlUp).Select
                
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
                                        
                'The "Last Name" Field
                If IsEmpty(textFile.Sheets("Registration Form").Range("D8").Value) = True Then
                textFile.Sheets("Registration Form").Range("D8").Value = "N/A"
                End If
                
                textFile.Sheets("Registration Form").Range("D8:F8").UnMerge
                textFile.Sheets("Registration Form").Range("D8").Copy
                Workbooks(1).Activate
                ActiveSheet.Select
                
                Range("C1000").Select
                Selection.End(xlUp).Select
                
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
                                        
                'The "Customer ID" Field
                If IsEmpty(textFile.Sheets("Registration Form").Range("I7").Value) = True Then
                textFile.Sheets("Registration Form").Range("I7").Value = "N/A"
                End If
                
                textFile.Sheets("Registration Form").Range("I7:J7").UnMerge
                textFile.Sheets("Registration Form").Range("I7").Copy
                Workbooks(1).Activate
                ActiveSheet.Select
                
                Range("D1000").Select
                Selection.End(xlUp).Select
                
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
                                         
    
                'The "Order ID" Field
                If IsEmpty(textFile.Sheets("Registration Form").Range("I8").Value) = True Then
                textFile.Sheets("Registration Form").Range("I8").Value = "N/A"
                End If
                
                textFile.Sheets("Registration Form").Range("I8:J8").UnMerge
                textFile.Sheets("Registration Form").Range("I8").Copy
                Workbooks(1).Activate
                ActiveSheet.Select
                
                Range("E1000").Select
                Selection.End(xlUp).Select
                
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
   
        
       'Save and close each document (note: this will prevent unwanted popups for saving and lcosing)
     
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        textFile.Close
    
    Next i
       
       'Display message box telling the user that the procces in completed
    MsgBox "Finished"
End Sub