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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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?
 

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
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.
 

Kasrift

New Member
Joined
Jul 22, 2014
Messages
5
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.
 

Kasrift

New Member
Joined
Jul 22, 2014
Messages
5
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.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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?
 

Kasrift

New Member
Joined
Jul 22, 2014
Messages
5
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.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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>
 

Kasrift

New Member
Joined
Jul 22, 2014
Messages
5
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.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

Forum statistics

Threads
1,089,519
Messages
5,408,758
Members
403,225
Latest member
funny_bunny

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top