Hi,

I am new with VBA and need to collect data from multiple excel workbooks using a macro. I need to know the region and contact source of each customer purchase form.The workbooks are saved under customer name which means that it varies and they are located in different folders all accessible vie Sharepoint as we are all using Office online to edit the customer forms. There is 4 type of forms completed depending on the customer region or type of purchase. The data I need to collect are in 2 merged cell in each document type (cell varies depending on the region of the customer)

The first data is the "contact source" :

Form QC: data is in merged cells F51 to K51
Form ON: data is in merged cells F48 to K48
Form ME: data is in merged cells F48 to K48
Form Kidney: data is in merged cells J56 to Q56

The "contact source" data is in a drop down list created with the "Data Validation" tool in excel 2013. The list is the following:

Internet,
Newspaper,
Yellow pages,
Television,
Poster,
Pamphlet,
Banner ad,
Radio,
Auction,
Friend/Family,
Regular Customer,
Garage,
Municipality,
Police,
Fire department

The second data is the region and they are located in the following cells of each forms:
Form QC: data is in merged cells H4 to L4
Form ON: data is in merged cells H4 to L4
Form ME: data is in merged cells H4 to L4
Form Kidney: data is in merged cells D9-G9

The "region" data is in a drop down list created with the "Data Validation" tool in excel 2013. The list is the following for the QC forms:
LAVAL:2225 Montée Masson (QC) H7E 4P2,
GATINEAU:1798 Maloney E. (QC) J8R 3Z4,
SHERBROOKE:1290 Chemin Dion (QC) J1R 0R8,
TROIS-RIVIÈRES:2905 Bl. St-Michel (QC) G9A 5E1,
MONTRÉAL:9160 Boul. Henri-Bourassa E.,
LÉVIS:1509 rue de l'Église (QC) G6Z 2L1

The list is the following for the ON forms:
OTTAWA: 120 Bentley (ON) K2E 6T9,
HAMILTON: 139 Windermere road (ON) L8H 3Y2,
BURLINGTON: 4350 Harvester road (ON) L7L 5S4

The list is the following for the ME forms:
BANGOR:2630 Broadway (ME) 04401

However the information is entered mannually for the "Kidney" form


Can any one help me how to write a macro to read data from merged cells.

Thanks in advance