Help to copy from multiple excel files to master for each excel name to special sheets name

[nhnn1986]

Hi all

I have folder with over 3.000 excel files and I want to have vba code check file name in folder, if found will copy to special sheet like this:
Check excel file:
- If Begin with A00024 and have both column (B) and column(C) will copy range(C9:E11) to sheet(A00024). For example file name like: A00024-99201001-99201001-202012-SI-M-02.xlsx or A00024-99201001-99201001-201801-SI-M-01. xlsx
- If Begin with A00044 and have both column (B) and column(C) will copy range(C9:F783) to sheet(A00044)
- If Begin with C00203 and have both column (B) and column(C) will copy range(C9:C31) to sheet(C00203)
- If Begin with G00864 and have both column (B) and column(C) will copy range(B9:I73) to sheet(G00864)
- If Begin with G03625 and have both column (B) and column(C) will copy range(B9:D156) to sheet(G03625)

As you see in images belove, each value in column(B) will have 36 value in column(C), so the number of file I have to copy very much. Please help me to do this by VBA code.
Thanks./.

Master.xlsm
	A	B	C	D	E	F
1					Check all file name in folder (over 3.000 excel files)
2	A00024	99902001	202012		Begin with A00024 and have both column (B) and column(C) will copy range(C9:E11) to sheet(A00024)
3	A00044	99902002	202011			A00024-99201001-99201001-202012-SI-M-02
4	C00203	99902003	202010			…..
5	G00864	99902004	202009			A00024-99201001-99201001-201801-SI-M-02
6	G03625	99902005	202008		Begin with A00044 and have both column (B) and column(C) will copy range(C9:F783) to sheet(A00044)
7		99902006	202007		Begin with C00203 and have both column (B) and column(C) will copy range(C9:C31) to sheet(C00203)
8		99902007	202006		Begin with G00864 and have both column (B) and column(C) will copy range(B9:I73) to sheet(G00864)
9		99902008	202005		Begin with G03625 and have both column (B) and column(C) will copy range(B9:D156) to sheet(G03625)
10		99902009	202004
11		99902010	202003
12		99902011	202002
13		99902012	202001
14		99902013	201912
15		99902014	201911
16		99902015	201910
17		99902016	201909
18		99902017	201908
19		99902018	201907
20		99902019	201906
21		99902020	201905
22		99902021	201904
23		99902022	201903
24		99902023	201902
25		99902024	201901
26			201812
27			201811
28			201810
29			201809
30			201808
31			201807
32			201806
33			201805
34			201804
35			201803
36			201802
37			201801
Main

 

[Marc L]

Hi,​

​

may be easily achieved if at least the column D contains the source range address to copy like in cell D2 for example C9:E11 …​

​

Remind your need and well elaborate it or you will have to amend any solution, not a concern if you are very confident with your VBA skills !​

 

[nhnn1986]

Thanks @Marc L for reply. My Folder over 3.000 excel files like this:
folder

 

[Marc L]

​

Any answer to my questions ? As guessing can't be coding !​

​

Now it depends also on at least on another forum where your create a similar thread …​

​