Excel VBA Macro to copy a range of file of files from one folder to another

[djgenesis13]

Hello,

I have been searching the forum for this and I can not find anything that does the job. The case is rather simple though.

I have a folder full of pictures (about 10000). I also have an excel file where in one sheet I have some filenames (without path) in a range of cells - not in one column only, the filenames are in a continuous range of cells and specifically in range AZ:BU (22 columns). This range has also got some empty cells and a header row. an exaple of a cell value is

"Baby_Monitor_with_Two_Way_Audio_Main.jpg".

<tbody>
</tbody>

I want to search a specific folder in my pc, find only the files in the range mentioned above and copy them to another specific folder. I have tried a sub found in the forum but I just keep getting errors '76' path not found

Sub Copy_Files()
oldpath = "C:\oldfolder\"
newpath = "C:\newfolder\"
For Each r In Range("az2", Range("bu" & Rows.Count).End(xlUp))
fn = Dir(oldpath & r)
If fn <> "" Then
FileCopy oldpath & r, newpath & r
End If
Next
End Sub

Thanks in advance

 

[BrianMH]

Try changing

Set rValues = Range("az2", GetLastCell(Range("AZ:BU"), 3))

to

Set rValues = Range("az2", GetLastCell(Range("AZ:BU"), [COLOR=#333333]xlByRows+xlByColumns[/COLOR]))

 

[djgenesis13]

works perfect.

One last addition I was considering.

Since I want to run this from another sheet (there are a lot of sheets in this workbook), how can I state in the code, in which sheet it should run?

The sheet name is "Imported"

Thanks

 

[BrianMH]

works perfect.

One last addition I was considering.

Since I want to run this from another sheet (there are a lot of sheets in this workbook), how can I state in the code, in which sheet it should run?

The sheet name is "Imported"

Thanks

Change

[COLOR=#333333]Set rValues = Range("az2", GetLastCell(Range("AZ:BU"), 3))[/COLOR]

to

[COLOR=#333333]Set rValues = Sheets("Imported").Range("az2", GetLastCell(Range("AZ:BU"), 3))[/COLOR]

 

[djgenesis13]

Brian you are the best. This helped me a lot. Thanks a lot !!!

 

[BrianMH]

Glad to help!