Macro to Copy Files To Other Folders


February 20th, 2013 by BenneTan BenneTan

As a training provider for Excel course, we constantly have to copy the working files from our master folders into the different computers. We used to do this physical from one computer to another until we make use of the dropbox app. We created and share multiple folders from our admin computer, one folder for one training computer. At the end of the course, we would have to extract the feedback forms from the respective folders after our participants have completed the form. Since we are not able to save into the same folder with the file name, we will have to name each feedback form with the name of our participants, extracted from the feedback form. Once completed, we have to go into individual dropbox folder and delete away all the used working files. The entire process is very manual and time consuming. With limited resources, we decided to create this macro to help us automate the entire process with the admin person having to activate each of the 4 functions 4 times. Instead of taking hours on the process, the macro can complete the entire process in a matter of minutes. This is a time saver for us. Since we have created this macro, we decided to share it with you as is for free. We hope you can also make use of it to increase your productivity.

Macro 1 – Transfer files to another folder

Located in the “ByFile” Worksheet
The button in this worksheet copies individual files into specified destination folders, and changing the name of the file (if needed).

Step 1: Source File Directory

Step 1a: Click on the Windows Explorer icon beside the Windows button.

Step 1b: Go to the folder where the files you want to copy are located.  Click on the place where it’s circled:

Step 1c: You should be able to see the full directory as shown below:

Step 1d: Press CTRL + C to copy or right-click & click Copy:

Step 1e: Paste this directory in cell A3 of the “ByFile” sheet.


Step 2: Source File Name to Copy

Step 2a: Fill in the file you want to copy.  Note that the extension of the file is also needed.  Go back to the Windows Explorer & click on the file you want.

 

Step 2b: File Extension

Note: If you know the file extension, ignore this step.  Otherwise right-click & choose Properties.

 

You should be able to see the file extension here as shown below:

Step 2c: File Name

Press F2 so that you can be in the Edit mode & copy the file name.  Copy this name.

Step 2d: Paste inside cell B3 of “ByFile” sheet.  And remember to type in the file extension.


Step 3: Destination File Directory

Step 3a: Repeat the steps made to find the source directory to now find the destination directory, & paste it in cell C3 of the “ByFile” sheet.

 

Step 4: Destination File Name

Step 4a: If you want the same file name you copied from, then copy cell B3 & paste over to D3.  Otherwise, type a file name you desire & remember to include the extension.

Step 5: Populate the next row for another file you wish to copy. You can do this for as many files as you like and after you finish.

Step 6: Run the Macro

Step 6a:  Press the button & you’re done!

Macro 2 – Transfer files to another folder

Located in the “ByFolder” Worksheet

The macro stored in the button in this worksheet copies all files in the folder into specified destination folders.

Step 1 – Identify the directory path of the folder you are copying from. 

Step 1a – Click on the Windows Explorer icon beside the Windows button.

Step 2 – Source File Directory

Step 2a: Go to the folder where the folders you want to copy are located.  Do not click inside the folder you want.  Click on the place where it’s circled:

You should be able to see the full directory as such:


Step 2b: Press CTRL + C to copy or right-click & click Copy:

Step 2c: Paste this directory in cell A3 of the “ByFolder” sheet.

Step 3: Destination Folder Name to Copy

Step 3a: You then have to fill in the folder you want to copy.  Go back to the Windows Explorer & click on the folder you want.

Step 3b: Press F2 so that you can be in the Edit mode & copy the folder name.  Copy this name.

 

 

 

 

 


Step 3c: Paste inside cell B3 of “ByFolder” sheet.

Step 4: Destination File Directory

Step 4a: Repeat the steps made to find the source directory to now find the destination directory, & paste it in cell C3 of the “ByFolder” sheet.

 

 

 

 

Step 5: Destination Folder Name

Step 5a: This will be the final folder you want to paste in.

 

 

 

Step 6: Fill up the subsequent rows with  folders you wish to copy.

Step 7: Run the Macro

Step 7a: Press the button & you’re done!

Macro 3 -Delete Files from the folders

The macro located in this worksheet deletes all files in the specified folder.

Step 1: Open Windows Explorer icon located beside the Windows button.

Step 2: Get File Directory

Step 2a: Go to the folder where the folders you want to delete are located.  Do not click inside the folder you want.  Click on the place where it’s circled:

Step 2b: You should be able to see the full directory as such:

Step 2c: Press CTRL + C to copy or right-click & click Copy:

Step 2d: Paste this directory in cell A3 of the “Delete” sheet.

Step 3: Destination Folder Name to Delete

Step 3a: Fill in the folder you want to delete.  Go back to the Windows Explorer & click on the folder you want.

Step 3b: Press F2 so that you can be in the Edit mode & copy the folder name.  Copy this name.

Step 3c: Paste inside cell B3 of “Delete” sheet.

Step 4: Fill in the subsequent row with more folders you wish to remove.

Step 5: Run the Macro

Step 5a: Press the button.

Step 5b: A window pops up to confirm if you want to delete the files.  Files will permanently be deleted if you click “Yes”.

Macro 4 – Copy and change file name on paste

The macro in this worksheet copies all files that starts with “Feedback –“ from the source folder specified & goes to the destination folder specified and paste it with the participant’s name automatically.

Step 1: Open Windows Explorer icon beside the Windows button.

Step 2: Source File Directory

Step 2a: Go to the folder where the course folders are located.  Click on the place where it’s circled:

Step 2b: You should be able to see the full directory as shown.

Step 2c: Press CTRL + C to copy or right-click & click Copy:

Step 2d: Paste this directory in cell A3 of the “Feedback” sheet.

Step 3: Source Folder

Step 3a: You then have to fill in the Course folder where the files are.  Go back to the Windows Explorer & click on the folder you want.

Step 3b: Press F2 so that you can be in the Edit mode & copy the folder name.  Copy this name.

Step 3c: Paste this directory in cell B3 of the “Feedback” sheet.

Step 4: Destination File Directory

Step 4a: Repeat the steps made to find the source directory to now find the destination directory, & paste it in cell C3 of the “Feedback” sheet.

Step 5: Destination Folder Name

Step 5a: This will be the final folder you want to paste in.

Step 6: Fill in the subsequent rows with folders that you wish to copy and paste with a different participant’s name.

Step 7: Run the Macro

Step 7a: Click the macro button & files will be consolidated into the destination file directory.

Step 7b: Those who have input names in the individual files will be saved using the “Computer name_Participant Name”.  Those who did not fill in the name will just have the Computer Name as the file name.

You can download a copy of the file in this link Macro to copy files.

Leave a Reply

You must be logged in to post a comment.

s2Member®