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]

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.

 

[luna007]

No Reply

 

[luna007]

Please help me