Look for value in cell then move file to correct folder

[Nick70]

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:

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

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.

 

[Fluff]

Try changing this

Dim Cellx As Long

to String.

 

[Nick70]

Now works perfectly.

Thanks,
N

 

[Fluff]

You're welcome & thanks for the feedback.