title: Copy/Rename Batch of Files, based on data in a worksheet
body: <p>I have a list in Column A with hyperlinks of the file-names to PDF files on a shared network drive which gets filtered and sorted often. I want to run a macro on a selected filtered range excluding hidden rows in the table. The macro would copy those files to a new location, and rename them based on data in the worksheet.</p>
  
  <p>Column A contains the hyperlinks file names including extension (e.g. Cell "A3" contains "15-P980_Vendor_15169_.pdf")
  Column B and E contains text based on formula to extract text from the file name. The underscores are separators. (Column C and D are hidden and are not used). So, Cell "B3" contains "15-P980", and Cell "E3" contains "Vendor" from the file name that's in Column A.</p>
  
  <p><a href="https://i.stack.imgur.com/Mmzmn.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Mmzmn.jpg" alt="enter image description here"></a></p>
  
  <p>Trying to rename the file in each row what's in Cell(, 5) + "_" + Cell(, 2) in the selected Range Rows.</p>
  
  <p>I'm selecting my range as =$A$3:$E$6.</p>
  
  <p>I am getting an Object required error. I'm having trouble writing the <code>For Each</code> part. Especially obtaining the file path, that is the <code>sourcePath =</code>. I figure I have to obtain the hyperlink address that's in Column A, then extract the file path from that, but not sure how to code that. Any help will be appreciated.</p>
  
  <pre><code>Sub CopyFile()
  ThisWorkbook.ActiveSheet.Unprotect
      On Error GoTo errHndl
      Dim xTitleId As String
      Dim sourcePath As String, destPath As String
      Dim sourceFile As String, destFile As String, sourceExtension As String
      Dim rng As Range, cell As Range, row As Range
  
  
      destPath = "C:\Users\\Desktop\Test\dst"
      sourceFile = ""
      destFile = ""
  
      xTitleId = "Copy/Rename Files"
      Set rng = ThisWorkbook.ActiveSheet.Application.Selection
      Set rng = ThisWorkbook.ActiveSheet.Application.InputBox("Range", xTitleId, rng.Rows, Type:=8)
      Set addr = rng.Cells(, 1)
  
      For Each row In rng.Rows
        sourcePath = addr.Hyperlinks(1).Address 
        sourceExtension = Split(row.Cells(, 1), ".")(1)
        sourceFile = sourcePath + row.Cells(, 1)
        destFile = destPath + row.Cells(, 5) + "_" + row.Cells(, 2) + "." +  sourceExtension
        File.Copy sourceFile, destFile, False
      Next row
  
  
      MsgBox "Operation was successful.", vbOKOnly + vbInformation, "Done"
      Exit Sub
  
  errHndl:
      MsgBox "Error happened while working on: " + vbCrLf + _
          sourceFile + vbCrLf + vbCrLf + "Error " + _
          Str(Err.Number) + ": " + Err.Description, vbCritical + vbOKOnly, "Error"
  
  End Sub
  </code></pre>