Import Multiple Excel FILES in to one MASTER worksheet

[nagovind]

Dear All,


I have an requirement which i'm unable to solve


There are 7 different worksheets with file name [7021.xlsm ; 7028.xlsm ; 7022.xlsm ; 7158.xlsm ; 7892.xlsm ; 7555.xlsm ; 7899.xlsm ]


There is one master worksheet with file name MASTERCONTROL.xlsm


In each of the 7 worksheets ...it contains the data in A1 cell, B1 cell and E1 cell


All the 7 worksheets and the mastercontrol worksheets are in the same folder in the location C:controls


I need a program in the master worksheet so that all worksheets (7 nos which contains one sheet only) has to be imported into master worksheet with the FILE NAME as SHEET NAME in the MASTERCONTROL.xlsm


Please advise

 

[bobhc]

Good day nagovind


Using the search box top right this link was one of many about your question


http://chandoo.org/wp/2010/02/19/excel-consolidate-data/

 

[nagovind]

bobhc,


Thank you for the reply


I got the code as below


Sub GetSheets()

Path = "C:Documents and SettingsawweDesktopeee"

Filename = Dir(Path & "*.xlsm")

Do While Filename <> ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Next Sheet

Workbooks(Filename).Close

Filename = Dir()

Loop

End Sub

 

[bobhc]

nagovind


Let the forum know if success or failure

 

[nagovind]

bobhc,


Yes it is working good


Only disadvantage is Multiple files has to be in separate folder and the master consolidated file should be in other folder otherwise it is reopening the already open master file from where the macro is executed


Rest is fine it is doing the job