Moving files based on the value of a cell?

[shannonmarie111]

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!!

 

[oldbrewer]

filename1

y

filename2

n

filename3

y

filename4

n

filename5

y

filename6

n

filename7

y

filename8

n

filename77

filename9

y

filename78

filename10

n

filename1

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]

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]

You could do it this way

Name	Copy
Chrysanthemum.jpg	y
Desert.jpg	y
desktop.ini	n
Hydrangeas.jpg	n
Jellyfish.jpg	y
Koala.jpg	y
Lighthouse.jpg	n
Penguins.jpg	n
Tulips.jpg	y

<tbody>
</tbody>

Here's a code you can use

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]

You could do it this way

Name	Copy
Chrysanthemum.jpg	y
Desert.jpg	y
desktop.ini	n
Hydrangeas.jpg	n
Jellyfish.jpg	y
Koala.jpg	y
Lighthouse.jpg	n
Penguins.jpg	n
Tulips.jpg	y

<tbody>
</tbody>

Here's a code you can use

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]

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

 

[shannonmarie111]

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? :

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

 

[Momentman]

You left out the backslash at the end of copyfrom and moveto. It should be

copyfrom = "C:\copyfrom\" 
moveto = "C:\moveto\"

 

[shannonmarie111]

You left out the backslash at the end of copyfrom and moveto. It should be

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]

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