Excel Macro to Combine data

[hishamash]

Hi ,

I have around 200 + Excel sheets in a folder. Each sheet is named as Club 1.xlsx, Club 2.xlsx, Club 3.xlsx and simultaneously till the last excel sheet.

I have a master data sheet(mds) with the name coordinator_data.xlsx. In the master data sheet i have 4 different tabs
Tab1 : coordinator
Tab2 : employee data
Tab3: Lookup_Club
Tab4: Lookup_Location

The below is the sample data which will be in source sheets club 1, club 2, club 3 etc

Coordinators Name
Phone Number
Commercial license number
club name according the commercial license
Facility location
بيانات العاملين / Employee Data
SlNo	Full Name	ID Number	Phone Number	Residence
1
2
3
4

Could you please help me with a macro to copy the below details

1) Copy certain fields from each source excel to the master data sheet coordinator tab ,

Club No will be Excel sheet number say if the excel sheet is Club 1, the Club No should be 1 , If the excel sheet is Club 2 the Club No should be 2 simultaneously for all the sheets.
DataType Should be Facility Coordinator data for each row created.

Data Type​

Club No.​

Coordinator name​

Phone​

Commercial​

Club name​

Facilty location​

State​

2) Copy employee data certain fields from source excel to the master data sheet employee data tab
Club No will be Excel sheet number say if the excel sheet is Club 1, the Club No should be 1 , If the excel sheet is Club 2 the Club No should be 2 simultaneously for all the sheets.
DataType Should be Employee Data each row created.

Data Type

Club No.

Full Name

ID No.

ID Source

Phone No.

Residence

3) Copy Club name data fields from source excel to master data sheet lookup_club tab

Club No.​

Club Name​

Club No will be Excel sheet number say if the excel sheet is Club 1, the Club No should be 1 , If the excel sheet is Club 2 the Club No should be 2 simultaneously for all the sheets.

4) Facility location data fields from source excel to master data sheet lookup_location

Location Name​

Please help me to make a macro and also very important is that each time a new excel is saved in the folder the master data sheet should be updated with the new data.



Thank You