Moving files based on the value of a cell?

shannonmarie111

New Member
Joined
Jan 22, 2015
Messages
15
Hi everyone. I'm really new to this kind of stuff but mu boss has asked me to come up with something. I have a spreadsheet with file names in column A and either a "Y" or a "N" in column B. All the files that correspond to he file names are in folder "C:\copyfrom". If the value in column B is "Y", I need to either copy or move (they don't really care which) that file to "C:\moveto". I have been looking at code I see online forever,trying to manipulate it so this works and am having zero luck. Can anyone help me? Thank you!!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
filename1y
filename2n
filename3y
filename4n
filename5y
filename6n
filename7y
filename8nfilename77
filename9yfilename78
filename10nfilename1
filename3
filename5
filename7
filename9
this little macro copied all filenames with a "y"
to the other list
I put 2 others in to represent an existing list
before I ran the macro
x = 9
For j = 1 To 10
If Cells(j, 2) = "y" Then x = x + 1: Cells(x, 12) = Cells(j, 1)
Next j
End Sub
ASTOUND YOUR BOSS !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
YOU MAY HAVE TO MANUALLY COPY THE LIST TO THE OTHER FOLDER NOW
BUT WHAT THE HECK """

<tbody>
</tbody>
 

shannonmarie111

New Member
Joined
Jan 22, 2015
Messages
15
LOL at "astound your boss" :)
I guess I still don't understand what the X and the J are, and how it moves the files in to the folder c:\moveto ?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You could do it this way

NameCopy
Chrysanthemum.jpgy
Desert.jpgy
desktop.inin
Hydrangeas.jpgn
Jellyfish.jpgy
Koala.jpgy
Lighthouse.jpgn
Penguins.jpgn
Tulips.jpgy

<tbody>
</tbody>

Here's a code you can use
Code:
Sub CopyFiles()
    copyfrom = "C:\Users\Public\Pictures\Sample Pictures\"
    moveto = "C:\Users\Public\Pictures\Sample Pictures\pix2\"
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("B" & i).Value = "y" Then
            FileCopy copyfrom & Range("A" & i).Value, moveto & Range("A" & i).Value
        End If
    Next i
End Sub

Change the "copyfrom" and "moveto" as necessary
 

shannonmarie111

New Member
Joined
Jan 22, 2015
Messages
15

ADVERTISEMENT

You could do it this way

NameCopy
Chrysanthemum.jpgy
Desert.jpgy
desktop.inin
Hydrangeas.jpgn
Jellyfish.jpgy
Koala.jpgy
Lighthouse.jpgn
Penguins.jpgn
Tulips.jpgy

<tbody>
</tbody>

Here's a code you can use
Code:
Sub CopyFiles()
    copyfrom = "C:\Users\Public\Pictures\Sample Pictures\"
    moveto = "C:\Users\Public\Pictures\Sample Pictures\pix2\"
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("B" & i).Value = "y" Then
            FileCopy copyfrom & Range("A" & i).Value, moveto & Range("A" & i).Value
        End If
    Next i
End Sub

Change the "copyfrom" and "moveto" as necessary

Thanks, I got an error "File not found" at "FileCopy copyfrom & Range("A" & i).Value, moveto & Range("A" & i).Value"?
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
please post your modified code and data as it appears in your worksheet
 

shannonmarie111

New Member
Joined
Jan 22, 2015
Messages
15

ADVERTISEMENT

please post your modified code and data as it appears in your worksheet

I changed the file paths to the actual paths on my local machine, I didn't change anything else, do I need to? :

Code:
Sub CopyFiles() 
  copyfrom = "C:\copyfrom" 
  moveto = "C:\moveto" 

  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row 
    If Range("B" & i).Value = "y" Then 
      FileCopy copyfrom & Range("A" & i).Value, moveto & Range("A" & i).Value 
    End If 
  Next i 
End Sub
 
Last edited by a moderator:

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You left out the backslash at the end of copyfrom and moveto. It should be
Rich (BB code):
copyfrom = "C:\copyfrom\" 
moveto = "C:\moveto\"

 

shannonmarie111

New Member
Joined
Jan 22, 2015
Messages
15
You left out the backslash at the end of copyfrom and moveto. It should be
Rich (BB code):
copyfrom = "C:\copyfrom\" 
moveto = "C:\moveto\"


I made that change, ran it and got no errors but nothing happened. I appreciate your help with this, I just don't understand what I'm doing wrong. :(
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
1. Can you post what your data in column A looks like?
2. Are your "y's" in column B in lower ot upper case?

To easily get the it right, please post sample data
 

Forum statistics

Threads
1,148,179
Messages
5,745,199
Members
423,932
Latest member
pablo2

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
Top