Add cell value to folder name

[leemcder]

Hello, I am using this macro to find files and move them to a folder. The folder created uses the contents in column A as the folder name. I'd like the folder to now use column A and column B as the folder name. I've tried a few things and it hasn't work. Please can someone help. Thank you


Sub FindFilesAndMove() Dim MyFolder As String, MyFile As String, srchStr As String, DestFoldFull As String, FSO As Object, rCell As Range
Dim DestFold As String

Set FSO = CreateObject("Scripting.Filesystemobject")
MyFolder = "Y:\Accounts Conveyancing Shared\Populated completion documents\" '<<< change to suit
DestFold = "Y:\Accounts Conveyancing Shared\Completions\Auto completion packs\" '<<< change to suit


For Each rCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
MyFile = Dir(MyFolder)
srchStr = rCell.Value & " - "
DestFoldFull = DestFold & rCell & "\"
MkDir DestFoldFull
Do While MyFile <> ""
If InStr(MyFile, srchStr) Then
FSO.MoveFile Source:=MyFolder & MyFile, Destination:=DestFoldFull & MyFile
End If
MyFile = Dir
Loop
Next rCell

MsgBox ("Pack Creation Successful")

End Sub


End Sub

[arnelgp]

change this to:

..
MyFile = Dir$(MyFolder & "*.*")

[leemcder]

Thank you for taking the time to reply. I have tried this and the folder name has not changed, apologies if I'm missing something, should I be changing anything else?


change this to:

..
MyFile = Dir$(MyFolder & "*.*")

[georgiboy]

It depends whether both cells make one folder name:

DestFoldFull = DestFold & rCell & rCell.Offset(, 1) & "\"

Or if different levels of folder then maybe:

DestFoldFull = DestFold & rCell & "\" & rCell.Offset(, 1) & "\"

Hope this helps

[leemcder]

Thanks Georgiboy, so I replaced DestFoldFull = DestFold & rCell & "" with DestFoldFull = DestFold & rCell & rCell.Offset(, 1) & "" this gives me a runtime error '76' path not found. Debug refers to MkDir DestFoldFull

Thank you


Thank you for taking the time to reply. I have tried this and the folder name has not changed, apologies if I'm missing something, should I be changing anything else?

[leemcder]

I've worked it out, column B is a date and it doesn't like date format with a / as can't save a file with this. Works perfectly with a .

Many thanks for your help! Much appreciated


Thanks Georgiboy, so I replaced DestFoldFull = DestFold & rCell & "" with DestFoldFull = DestFold & rCell & rCell.Offset(, 1) & "" this gives me a runtime error '76' path not found. Debug refers to MkDir DestFoldFull

Thank you

[georgiboy]

Glad you worked it out, thanks for posting the fix.

George