Hi,

I am trying to automate moving files and I cannot get it to work. I was able to create the new folders automatically but not moving the invoices.

I have an Excel file where I have sheet called serviceinvoice
In A column I have user number and in B column I have invoice number

In cell D1 I have a file path.

In that folder I have pdf invoices and folders per user

The name of the pdf invoice starts with the invoice number (12 characters)
The name of the user folder starts with the user number (4 characters)

Process that I am looking for is:
1. check the invoice number from excel sheet
2. find the pdf file that starts with that number
3. go and get a user number from column A
4. find the subfolder where the name starts with this number
5. move the invoice to this folder
6. if folder cannot be found, skip to next invoice
7. do this to all invoices listed in the Excel sheet.

This is how far I have got, but it doesn't work and I don't understand why. (And ChatGPT is clueless)

I have checked that the invoices have the same numbers and that the subfolders are there. Please help!

Sub MoveInvoices()

Dim ws As Worksheet
Dim folderPath As String
Dim invoiceNumber As String
Dim userNumber As String
Dim invoiceFileName As String
Dim userFolder As String

' Set worksheet
Set ws = ThisWorkbook.Sheets("serviceinvoice")

' Get folder path from cell D1
folderPath = ws.Range("D1").Value

' Loop through rows
For i = 3 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
invoiceNumber = Left(ws.Cells(i, 2).Value, 12) ' Use only the first 12 characters
userNumber = Left(ws.Cells(i, 1).Value, 4) ' Use only the first four characters

' Construct file paths
invoiceFileName = invoiceNumber & "*" ' Assumes invoice files are PDFs
userFolder = userNumber ' The user folder starts with the user number

' Check if invoice file exists
If Dir(folderPath & "" & invoiceFileName) <> "" Then

' Check if user folder exists, if not, skip this invoice
If Dir(folderPath & "" & userFolder, vbDirectory) <> "" Then

' Move invoice to user subfolder
FileCopy folderPath & "" & invoiceFileName, folderPath & "" & userFolder & "" & invoiceFileName
Kill folderPath & "" & invoiceFileName ' Remove original file

End If
End If

Next i

End Sub