Copy a file and re-name based on a list of numbers in an excel column.

[PatMyer]

How to copy a file and rename based column contents​

I have on my on desktop one file (.jpg) that is called BlankImage.jpg inside a folder called "Test".
In excel in column A contains a long list of parts. is there an automated way that the BlankImage file on my desktop folder Test can be copied and renamed for each item on column A into the folder on my desktop (copy and re-name the BlankImage file multiple times based on the list of items in Column A)?

Column A
001.001.00
002.001.00
003.001.00
004.001.00

 

[Domenic]

The following code assumes that the worksheet containing your list of parts is the active sheet, and that the data starts at Row 2. Note that if a filename by the same name already exists in the folder, a date and time stamp is added to the filename. You may want to do otherwise. If you simply want to ignore those files in this case, simply comment out or delete the following line of code...

FileCopy folderName & imageFilename, folderName & fileNames(i, 1) & "_" & Format(Now(), "yyyy-mm-dd_hh-mm-ss") & fileExt 'add date and time stamp to filename

Here's the macro . . .

Option Explicit

Sub CreateImageFileCopies()

    Dim folderName As String
    folderName = "c:\users\domta\desktop\test\" 'change the path to folder accordingly
    
    If Right(folderName, 1) <> "\" Then
        folderName = folderName & "\"
    End If
    
    Dim imageFilename As String
    imageFilename = "BlankImage.jpg"
    
    Dim fileExt As String
    fileExt = Mid(imageFilename, InStrRev(imageFilename, "."))
    
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If lastRow < 2 Then
        MsgBox "No data found!", vbExclamation
        Exit Sub
    End If
    
    Dim fileNames As Variant
    fileNames = Range("A2:A" & lastRow).Value
    
    Dim i As Long
    For i = LBound(fileNames) To UBound(fileNames)
        If Len(fileNames(i, 1)) > 0 Then
            If Len(Dir(folderName & fileNames(i, 1) & fileExt, vbNormal)) = 0 Then
                FileCopy folderName & imageFilename, folderName & fileNames(i, 1) & fileExt
            Else
                FileCopy folderName & imageFilename, folderName & fileNames(i, 1) & "_" & Format(Now(), "yyyy-mm-dd_hh-mm-ss") & fileExt 'add date and time stamp to filename
            End If
        End If
    Next i
    
    MsgBox "Completed!", vbExclamation
    
End Sub

Hope this helps!