VBA Copy and Rename

Kasrift

New Member
Joined
Jul 22, 2014
Messages
5
Hello Everyone,

This is my first post. I'm rather novice at VBA, but I found a post elsewhere that had some code to copy and rename files. I would like to utilize this code, but I need to be able to copy and rename a range of cells, and the code posted was only for one cell. Can anyone suggest how to either loop this VBA code or modify it for a range?

Sub CopyRenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String


'Source directory
src = Range("B3")
'Destination directory
dst = Range("D3")
'File name
fl = Range("B6")
'Rename file
rfl = Range("D6")




On Error Resume Next
FileCopy src & "\" & fl, dst & "\" & rfl
If Err.Number <> 0 Then
MsgBox "Copy error: " & src & "\" & rfl
End If
On Error GoTo 0


End Sub





Thanks in advance.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
What is it you're trying to do exactly? Copy and paste the cells into a new workbook...or name the workbook based on cells?
 
Upvote 0
Here are a couple of examples for a range larger than one cell:

Range("A:E") will include entire columns

Range("A1:B9") would include the cells named, and everything within that range.
 
Upvote 0
I'm trying to copy the files listed in one directory and move and copy and rename the file to another directory. The original code was posted here, but it only works for one cell. Copy/Rename a file (excel vba) | Get Digital Help - Microsoft Excel resource

Basically I have a list of file names from a directory, and those files I want to copy and rename into a new directory. Some of the files may need to have duplicate copies made from one file name (but be renamed differently). The list of the file names and what they should be renamed into is provided from a .csv generated from a SAS report.
 
Upvote 0
Hey Kentetsu,

I tried changing the range to be Range("B6:B100"). I don't think it works because of the code below where you are copying the file names. The variable fl can't be a range for this. I have very limited coding experience for VBA, but I understand some code logic since I work with other languages.

I'm thinking you would have to set a integer and loop it. I searched around the threads and internet and see some examples of that, but the more complicated part is setting the loop for two variables, since I have two columns. I'm hoping someone has a more elegant/simple solution.
 
Upvote 0
So...I think I get what you're trying to do....maybe. Your range values don't make any send to me though. The file name is three cells below the directory?
 
Upvote 0
Ya, that is kind of arbitrary. I don't care where the file names start. I just used the same format as the link I posted. I have been playing around with updating the vba macro in a workbook that includes some test files in column B starting at row 3, and the updated file name starting at column D row 3, but it only copies the first listed cell.

I guess looking at it a second time, the directory lines should probably not be set as ranges though.
 
Upvote 0
They can be, it doesn't matter where they are, just that it's consistent. Here's the code and a sample table:

Code:
Sub copyAndRenameWithRange()

    Dim sourceDir As String
    Dim destDir As String
    
    Dim sourceName As String
    Dim destName As String
    
    Dim startRow As Integer
    Dim endRow As Long
    
    startRow = 3
    endRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row


    For x = startRow To endRow
    
        sourceDir = Cells(x, 2)
        sourceName = Cells(x, 3)
        
        destDir = Cells(x, 4)
        destName = Cells(x, 5)
        
        On Error Resume Next
            FileCopy sourceDir & "\" & sourceName, destDir & "\" & destName
            If Err.Number <> 0 Then
                MsgBox "Copy error: " & sourceDir & "\" & destName
            End If
        On Error GoTo 0
    
    Next x




End Sub

ABCDEF
1
2File DirFile NameNew DirNew Name
3X:\Folderpath.xlsxX:\Folder\Documentspath2.xlsx
4

<tbody>
</tbody>
 
Upvote 0
Thanks NeonRedSharpie,

I was mid post asking if the number after the variables was the column number, since I was getting an error and that is what it looks like was happening. I'm assuming you edited the post. Thank you very much.
 
Upvote 0
Yea, I think I was off by a column. I called Column B (3) when it's 2. Thought I got the edit in there before anyone could see...guess not.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top