How to make folder, sub-folder and copy or move Excel file? Folder name should be based on cell range (C9:F9). Another sub folder name should be based

luna007

New Member
Joined
Jan 6, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I have received approximately 4000 excel files of my 1000 clients.

All these clients are fill Material Request Form every month. Sometimes if any of the month client do not have any material request so he do not fill Material Request Form for that month.

Each file have only one excel sheet namely “Sheet1”.

The file name is as follows: (C00000000000001 is Client ID).

PURREQ_C00000000000001_2017 April

PURREQ_C00000000000001_2017 May

PURREQ_C00000000000001_2017 June

PURREQ_C00000000000001_2017 July

PURREQ_C00000000000001_2017 August

PURREQ_C00000000000001_2017 September

PURREQ_C00000000000001_2017 October

PURREQ_C00000000000001_2017 November

PURREQ_C00000000000001_2017 December

PURREQ_C00000000000001_2017 January

PURREQ_C00000000000001_2017 February

PURREQ_C00000000000001_2017 March

PURREQ_C00000000000001_2018 April

PURREQ_C00000000000001_2018 May

PURREQ_C00000000000001_2018 June

PURREQ_C00000000000001_2018 August

PURREQ_C00000000000001_2018 September

PURREQ_C00000000000001_2018 October

PURREQ_C00000000000001_2018 November

PURREQ_C00000000000001_2018 December

PURREQ_C00000000000001_2018 January

PURREQ_C00000000000001_2018 February

PURREQ_C00000000000001_2018 March

PURREQ_C00000000000001_2019 April

PURREQ_C00000000000001_2019 May

PURREQ_C00000000000001_2019 June

PURREQ_C00000000000001_2019 July

PURREQ_C00000000000001_2019 August

PURREQ_C00000000000001_2019 September

PURREQ_C00000000000001_2019 November

PURREQ_C00000000000001_2019 December

PURREQ_C00000000000001_2019 January

PURREQ_C00000000000001_2019 February

PURREQ_C00000000000001_2019 March

PURREQ_C00000000000002_2017 April

PURREQ_C00000000000002_2017 May

PURREQ_C00000000000002_2017 June

PURREQ_C00000000000002_2017 July

PURREQ_C00000000000002_2017 August

PURREQ_C00000000000002_2017 September

PURREQ_C00000000000002_2017 October

PURREQ_C00000000000002_2017 November

PURREQ_C00000000000002_2017 December

PURREQ_C00000000000002_2017 January

PURREQ_C00000000000002_2017 February

PURREQ_C00000000000002_2017 March

PURREQ_C00000000000002_2018 April

PURREQ_C00000000000002_2018 May

PURREQ_C00000000000002_2018 June

PURREQ_C00000000000002_2018 July

PURREQ_C00000000000002_2018 August

PURREQ_C00000000000002_2018 September

PURREQ_C00000000000002_2018 October

PURREQ_C00000000000002_2018 November

PURREQ_C00000000000002_2018 December

PURREQ_C00000000000002_2018 January

PURREQ_C00000000000002_2018 February

PURREQ_C00000000000002_2018 March

PURREQ_C00000000000002_2019 April

PURREQ_C00000000000002_2019 May

PURREQ_C00000000000002_2019 June

PURREQ_C00000000000002_2019 July

PURREQ_C00000000000002_2019 August

PURREQ_C00000000000002_2019 September

PURREQ_C00000000000002_2019 October

PURREQ_C00000000000002_2019 November

PURREQ_C00000000000002_2019 December

PURREQ_C00000000000002_2019 January

PURREQ_C00000000000002_2019 February

PURREQ_C00000000000002_2019 March

And so-----on upto C00000000001000. So it is near about 4000 excel files in one folder i.e. CLIENT which was I received.

Now I want to sort all these excel files which I kept in CLIENT Folder.

  • I want to create SUB FOLDERS within CLIENT FOLDER automatically.
  • Each SUB FOLDER Name should be based on cell range (C9:F9). Every Excel file have client name in this cell range that is (C9:F9).
  • In above mentioned SUB FOLDER create automatically Financial Yearwise (Financial Year started From APRIL TO MARCH) SUB FOLDERS i.e 2017-18, 2018-19, 2019-20 and so---on accordingly such types of files available in CLIENT FOLDER.
  • Then I want to copy or move all 4000 excel files automatically financial yearwise folders from CLIENT FOLDER to subfolder ABC and sub-sub folder 2017-18 or 2018-19 and so on.
For example



C:\Client\ABC\2017-18\
PURREQ_C00000000000001_2017 April

PURREQ_C00000000000001_2017 May

PURREQ_C00000000000001_2017 June

PURREQ_C00000000000001_2017 July

PURREQ_C00000000000001_2017 August

PURREQ_C00000000000001_2017 September

PURREQ_C00000000000001_2017 October

PURREQ_C00000000000001_2017 November

PURREQ_C00000000000001_2017 December

C:\Client\ABC\2018-19\ PURREQ_C00000000000001_2018 April

PURREQ_C00000000000001_2018 May

PURREQ_C00000000000001_2018 June

PURREQ_C00000000000001_2018 August

PURREQ_C00000000000001_2018 September

PURREQ_C00000000000001_2018 October

PURREQ_C00000000000001_2018 November

PURREQ_C00000000000001_2018 December

PURREQ_C00000000000001_2018 January

PURREQ_C00000000000001_2018 February

PURREQ_C00000000000001_2018 March

C:\Client\ABC\2019-20\ PURREQ_C00000000000001_2019 May

PURREQ_C00000000000001_2019 June

PURREQ_C00000000000001_2019 July

PURREQ_C00000000000001_2019 August

PURREQ_C00000000000001_2019 September

PURREQ_C00000000000001_2019 November

PURREQ_C00000000000001_2019 December

PURREQ_C00000000000001_2019 January

PURREQ_C00000000000001_2019 February

PURREQ_C00000000000001_2019 March

C:\Client\XYZ\2017-18\ PURREQ_C00000000000002_2017 April

PURREQ_C00000000000002_2017 May

PURREQ_C00000000000002_2017 June

PURREQ_C00000000000002_2017 July

PURREQ_C00000000000002_2017 August

PURREQ_C00000000000002_2017 September

PURREQ_C00000000000002_2017 October

PURREQ_C00000000000002_2017 November

PURREQ_C00000000000002_2017 December

PURREQ_C00000000000002_2017 January

PURREQ_C00000000000002_2017 February

PURREQ_C00000000000002_2017 March

C:\Client\XYZ\\2018-19\ PURREQ_C00000000000002_2018 April

PURREQ_C00000000000002_2018 May

PURREQ_C00000000000002_2018 June

PURREQ_C00000000000002_2018 July

PURREQ_C00000000000002_2018 August

PURREQ_C00000000000002_2018 September

PURREQ_C00000000000002_2018 October

PURREQ_C00000000000002_2018 November

PURREQ_C00000000000002_2018 December

PURREQ_C00000000000002_2018 January

PURREQ_C00000000000002_2018 February

PURREQ_C00000000000002_2018 March

And so---on for 1000 clients.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,127,134
Messages
5,622,930
Members
415,941
Latest member
georgiana686

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top