Create a loop that copies files to a new location

[cjouban]

I have a sheet with 3000 links to different pdf files. All listed in the same column. I'm trying to copy each file to the same folder. This is what I have so far. I works when I define each row but I need it to move to the next row. Any help is much appropriated.

Private Sub File_Copy()

Dim copyfrom As String
Dim copyto As String

copyfrom = Sheets("data").Range("J5")
copyto = Sheets("data").Range("C5") & " " & Sheets("data").Range("H5")

FileCopy copyfrom, "J:\Procurement\Purchase Orders\PO Report Link" & copyto & ".pdf"

End Sub

 

[Domenic]

The following macro will start at Row 5 until the last used row in Column C...

Private Sub File_Copy()

    Dim copyfrom As String
    Dim copyto As String
    Dim lastRow As Long
    Dim i As Long
    
    With Sheets("data")
        lastRow = .Cells(.Rows.Count, "c").End(xlUp).Row
        For i = 5 To lastRow
            copyfrom = .Cells(i, "j").Value
            copyto = .Cells(i, "c").Value & " " & .Cells(i, "h").Value
            FileCopy copyfrom, "J:\Procurement\Purchase Orders\PO Report Link" & copyto & ".pdf"
        Next i
    End With


End Sub

Hope this helps!

 

[cjouban]

I love this forum. You guys/gals rock. Made one small edit. Added a \ to the copyto file path. Works great now. Thank you

The following macro will start at Row 5 until the last used row in Column C...

Private Sub File_Copy()

    Dim copyfrom As String
    Dim copyto As String
    Dim lastRow As Long
    Dim i As Long
    
    With Sheets("data")
        lastRow = .Cells(.Rows.Count, "c").End(xlUp).Row
        For i = 5 To lastRow
            copyfrom = .Cells(i, "j").Value
            copyto = .Cells(i, "c").Value & " " & .Cells(i, "h").Value
            FileCopy copyfrom, "J:\Procurement\Purchase Orders\PO Report Link\" & copyto & ".pdf"
        Next i
    End With


End Sub

Hope this helps!

 

[Domenic]

That's great, glad I could help.

Cheers!