'This function loops through all the files listed in the sheet and
 'rename them as per details provided by user
 Public Sub RenameFiles()
     '
     'Variable declaration
     Dim lCounter As Long
     Dim lInnerCounter As Long
     Dim bHasError As Boolean
     Dim strPath As String
     '
     'Clear Status field
     Sheet1.Range("E7:E" & Sheet1.Rows.Count).ClearContents
     '
     'Check if New File Name is not left blank
     bHasError = False
     For lCounter = 7 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
         If Trim(Sheet1.Range("D" & lCounter).Value) = "" Then
             Sheet1.Range("E" & lCounter).Value = "New File Name cannot be left blank"
             bHasError = True
         End If
     Next
     If bHasError = True Then
         MsgBox "There are few validation errors." & vbNewLine & vbNewLine & "Please check column E (Status) for details.", vbInformation
         Exit Sub
     End If
     '
     'Check if there are duplicate file names
     For lCounter = 7 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
         For lInnerCounter = lCounter + 1 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
             If Trim(LCase(Sheet1.Range("D" & lCounter).Value)) = Trim(LCase(Sheet1.Range("D" & lInnerCounter).Value)) Then
                 Sheet1.Range("E" & lCounter).Value = "Duplicate File Name"
                 bHasError = True
                 Exit For
             End If
         Next
     Next
     If bHasError = True Then
         MsgBox "There are few validation errors." & vbNewLine & vbNewLine & "Please check column E (Status) for details.", vbInformation
         Exit Sub
     End If
     '
     'Store path in a variable and add slash to it
     strPath = Sheet1.Range("C3").Value
     If Right(strPath, 1) <> "/" And Right(strPath, 1) <> "\" Then
         strPath = strPath & "\"
     End If
     'Rename files
     For lCounter = 7 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
         On Error GoTo Error_FileRename
         '
         'Use Name function to rename file
         Name strPath & Sheet1.Range("C" & lCounter).Value As strPath & Sheet1.Range("D" & lCounter).Value
         '
         'Update status column as success
         Sheet1.Range("E" & lCounter).Value = "Success"
         '
         On Error GoTo 0
     Next
     '
     'Show confirmation to user
     MsgBox "Done", vbInformation
     '
     Exit Sub
     '
 'Error Handler if file rename produces error
 Error_FileRename:
     MsgBox "An error occurred while renaming file '" & Sheet1.Range("C" & lCounter).Value & "'." & vbNewLine & vbNewLine & "Error: " & Err.Description, vbCritical
     Sheet1.Range("E" & lCounter).Value = "Failed"
     Exit Sub
End Sub