Selected Answer
Hello Hasson,
There is something fundamentally wrong with your idea: it doesn't save time. By the time you set up the worksheet to run the code - once you have the code - the job can already be completed without code. The more files you have the more time you save by not having to prepare the workbook.
In Windows Explorer you can select multiple files in two ways.
- Click on a file you want to transfer. Then hold down the SHIFT key and select another file. All the files between the first click and the second will become selected.
- Click on a file you want to transfer. Then hold down the CONTROL key and select another file. Both files will be selected. Continue to holding down the CONTROL key and select as many files as you want.
Clock on an already selected file again to deselect it.
You can use the second method after the first to select a big block of file names and then add more files from other parts of the list. Or youi can select a big block and then deselect some of them.
Now you can press COPY, go to another directory folder and press PASTE to make copies of the selected files there. Or you can use CUT and PASTE to move the files.
The use of a worksheet and VBA would make sense if you have a list of files from one directory and you want to move the files selectively to number of other directories, like all PDF files to directory A, all JPG files to Directory B, or by their file names or whatever. To set up this kind of list you would first have to have a list of destination folders, for example, different folder names in column D. But for 3 files no VBA is needed and for 100 files writing 100 destination paths is too much work. Therefore you need a list of folder paths and refer to them by only "A", "B" and "C" or "1", "2", 3".
Secondly, you would need rules by which files and folders are matched to each other. Your question includes no such rules. Therefore no intelligent code can be written.
If you want to persist in using VBA for this task the central code you will need is this:-
Name [old Name]
As
[new Name]
Both old name and new name must be complete, full file names consisting of Drive:Path\FileName.Extension. It seems that you only want to change the path. The function below will exchange an existing path for a new one. You can run the code from the Immediate pane with MsgBox NewFileName.
Function NewFileName() As String
Dim FileName As String
Dim NewPath As String
Dim Sp() As String
NewPath = "C:\Users\PC WORLD\Destop\SSS\" ' incl final backslash
FileName = ThisWorkbook.FullName
Sp = Split(FileName, "\")
FileName = Sp(UBound(Sp)) ' discard the old path
Sp = Split(NewPath, "\") ' the final blackslash creates a blank element
Sp(UBound(Sp)) = FileName
NewFileName = Join(Sp, "\")
End Function
You can easily expand the utility of this function to let it retrieve names and paths from your worksheet or feed both or one of these strings as arguments in the function call.