Move file to new folder based on cell value, create folder if not already exist

[technician12]

i'm very new to VBA, and would like things to stay ELI5 ( explain like i'm 5 ).

any simple way to do this?

If I3 = "Yes"
then move file in "E3" to Folder from B7 + \Approved - Ex H:\VBA\Approved
then afterward remove "Yes" from I3 ( so it doesent move all )

and if the folder doesent exist, then create it.

[gmayor]

It depends on your definition of simple. If copy and paste is included in that definition then you need a macro :).


Sub MoveFile()
'Graham Mayor - https://www.gmayor.com - Last updated - 30 Nov 2018
Dim fso As Object
Dim iPath As Long
Dim vPath As Variant
Dim strPath As String, strOldPath As String
Dim strName As String
Dim xlSheet As Worksheet
Set xlSheet = ActiveSheet
With xlSheet
strPath = .Range("A7") & "\Approved" 'assign the target path to a string
If UCase(.Range("I3")) = "YES" Then 'check if the move is approved
Set fso = CreateObject("Scripting.FileSystemObject")
strOldPath = .Range("E3") 'assign the original file path to a string
If fso.FileExists(strOldPath) Then
'extract the filename from the full name
strName = Split(strOldPath, "\")(UBound(Split(strOldPath, "\")))
'ensure the target path exists and create it if it doesn't
vPath = Split(strPath, "\")
strPath = vPath(0) & "\"
For iPath = 1 To UBound(vPath)
strPath = strPath & vPath(iPath) & "\"
If Not fso.FolderExists(strPath) Then MkDir strPath
Next iPath
'move the file
Name strOldPath As strPath & strName
Beep
MsgBox "File moved to " & strPath
Else
Beep
MsgBox strOldPath & " not found"
End If
End If
End With
Set xlSheet = Nothing
Set fso = Nothing
End Sub

[technician12]

does not seem to work ( i'v fixed the A7 to B7 in the StrPath ).
besides, i need this to work for all lines below aswell.

[technician12]

it works, just need to make it apply to all cells in "I" row now.
anyone kind enough to edjucate educate a "beginner" ?

[gmayor]

You will have to explain what you mean by
apply to all cells in "I" row, There is only one row in the I Column? The macro was programmed to work with the information you provided.

[technician12]

i mean the macro should apply to all of the cells between example: "I3:I100"
sorry for not expressing clearly.

still new to VBA, and learning all the correct terminology is a first step :)

[technician12]

anyone willing to assist :) ?