Look for value in cell then move file to correct folder

Nick70

Board Regular
Joined
Aug 20, 2013
Messages
190
Hi,

I am using a previous code given to me to sort files in different folders but unfortunately it does not seem to work for new criteria.

I need a code that opens files in a specific folder and then depending on whether the cell value in Cells(2, 3) is "ClientA" or "ClientB", the file will be moved to folder ClientA or folder ClientB.

Code below:
VBA Code:
Sub Sort()

Dim fPathSrc As String
    Dim fPathA As String
    Dim fPathB As String
    Dim strFile As String
    Dim Cellx As Long
  
    Application.ScreenUpdating = False

'   Set filepaths
    fPathSrc = "C:\Users\user\Desktop\Folder\Macros\"           'Source file path
    fPathA = "C:\Users\user\Desktop\Folder\Macros\ClientA\"     'Path for ClientA
    fPathB = "C:\Users\user\Desktop\Folder\Macros\ClientB\"     'Path for ClientB
  
'   Loop through all Excel files in source path
    strFile = Dir(fPathSrc & "*.xl*")
    Do While Len(strFile) > 0
'       Open file
        Workbooks.Open Filename:=fPathSrc & strFile
'       Look at Cellx
        Cellx = Cells(2, 3).Value
'       Close workbook
        ActiveWorkbook.Close
'       Move to correct folder
        Select Case Cellx
            Case "ClientA"    'ClientA
                Name fPathSrc & strFile As fPathA & strFile
            Case "ClientB"    'ClientB
                Name fPathSrc & strFile As fPathB & strFile
        End Select
'       Go to next file
        strFile = Dir
    Loop
  
    Application.ScreenUpdating = True
  
    MsgBox "Macro done!"
  
End Sub

Variable Cellx is cell C2 (or cells(2, 3)), if this cell has text ClientA then move file to folder ClientA otherwise if cell has text ClientB move file to folder ClientB.

I get an error message at line

Code:
Cellx = Cells(2, 3).Value

Can someone me help fix this code or send me new code that works (either way would be great)?

Thanks,
N.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,057
Office Version
  1. 365
Platform
  1. Windows
Try changing this
VBA Code:
Dim Cellx As Long
to String.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,057
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,087
Messages
5,576,035
Members
412,694
Latest member
Deaf1Too
Top