I'll give you enough to set you on the path.
(you'll learn better this way (grin))
First, there is a "collection" of worksheet names in every excel  file.
once you open a file, you can loop through all of the sheets in the file.
Another thing you should get used to is that the two terms:
ActiveWorkbook and ThisWorkbook do not necessarily mean the same thing.
ThisWorkbook is the name of the workbook that you're writing the code in.
ActiveWorkbook is the name of the workbook that currently has focus.
so.. say you write a macro/function/sub in a "master" or "reporting" workbook.
then ThisWorkbook.name is always equal to the name of the master file.
If, during the execution of the macro, you open another file, then while
that file is "active", then Activeworkbook.name is the name of THAT file.

given that criteria,then this code:
'=========================================
Option Explicit
Global MasterFileName, msg
Global RowLoc
Sub test()
MasterFileName = ThisWorkbook.Name
msg = ""
RowLoc = 1
Get_Data ("C:\temp\TestBook1.xls")
Get_Data ("C:\temp\TestBook2.xls")
MsgBox msg
End Sub
Function Get_Data(FullFileName)
Dim Shtno
Workbooks.Open Filename:=FullFileName
For Shtno = 1 To ActiveWorkbook.Sheets.Count
RowLoc = RowLoc + 1
Workbooks(MasterFileName).Sheets("Sheet1").Cells(RowLoc, 1) =
ActiveWorkbook.Name
Workbooks(MasterFileName).Sheets("Sheet1").Cells(RowLoc, 2) =
ActiveWorkbook.Sheets(Shtno).Name
msg = msg & ActiveWorkbook.Name & "." & Sheets(Shtno).Name & Chr(13)
Next Shtno
ActiveWorkbook.Close savechanges:=vbNo
End Function

'=========================================
will enter the file names and sheet  names in cells on Sheet1 of the "master"
file.
now, it is a question  of copying the data from the cells on the files into the
"master" file...

give it a whirl and see what you come up with.

Let me know if you'd like more help.