VBA Tool to seperate reports and populate list of excel files in a folder

**jackreacher39uk** · 07-01-2013, 11:30 PM

Hello I need help with a VBA Macro to work through column a (cost centre) in 2 tabs move these files to a new directory, look at the remaining files move these to a new destination folder and list these in an excel spread sheet.

Firstly, it will look at the 1.Distribution list (tab 1) and carry out an 'exact
match' against cost centre (please note this number can be varying in size and can be either numeric, or alphanumeric or a combination, this list will change from month to month) and move the excel files with these numbers from the its source folder to its destination folder:

Source folder "C:\Users\TEST\Desktop\Report Seperator\1.All Reports"
Destination folder called C:\Users\TEST\Desktop\Report Seperator\2.Distribution List

Secondly, it will look at 2.Excusions (tab 3) and carry out an 'exact match' against cost centres (please note this number can be varying in size and can be either numeric, or alphanumeric or a combination, this list will change from month to month) and move the excel files with these numbers from the its source folder to its destination
folder:

Source folder "C:\Users\TEST\Desktop\Report Seperator\1.All Reports"
Destination folder called C:\Users\TEST\Desktop\Report Seperator\3.Exclusions

Finally, all the reports left within the source folder will need be moved to a new
destination

Source folder "C:\Users\TEST\Desktop\Report Seperator\1.All Reports"
Destination folder C:\Users\TEST\Desktop\Report Seperator\3.New Centres.

I would then like the script to look at Destination folder C:\Users\TEST\Desktop
\Report Seperator\3.New Centres and capture these names within the tab 3.New Centres.
I have drafted the input and output files in excel, if you require these please drop
me a line.

Many thanks for your help.

P.S I have created the template if anyone needs to look at this.

**JapanDave** · 07-02-2013, 12:15 AM

Post the sample workbook.

**jackreacher39uk** · 07-02-2013, 12:24 AM

hello, does anyone know what site I could use to upload the workbook to so I can link her? I am not able to find a direct upload icon here?

Many thanks

**JapanDave** · 07-02-2013, 12:59 AM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**jackreacher39uk** · 07-02-2013, 02:34 AM

Report_Seperator.xlsx 2345.xlsx

**jackreacher39uk** · 07-02-2013, 02:44 AM

I am having problems attaching a zip of the empty excel files, but basically, 2345 represents a centre number and one of the work books on a tab 1 one.

**JapanDave** · 07-02-2013, 02:55 AM

It has been a long day and the brain is not functioning as I would hope. Are you looking into directories to get the file and move them to a new one when you get a match?

**jackreacher39uk** · 07-02-2013, 05:31 AM

Yes that is correct, all reports will be stored in one directory initially,

C:\Users\TEST\Desktop\Report Seperator\1.All Reports

These need to be moved to

A) C:\Users\TEST\Desktop\Report Seperator\2.Distribution List

B) C:\Users\TEST\Desktop\Report Seperator\3.Exclusions

C) C:\Users\TEST\Desktop\Report Seperator\4.New Centres.

Please note, tab C does not contain a list, so the script will need to move the files into a new directory and populate the list as per template.

Many thanks