Transfer the data from the multiple files into 1 worksheets

[Ann_BBO]

Now, i want to transfer the data from the multiple (same type) workbooks into 1 worksheet. Before this, i had done the User Interface which can extract the means data from the 1 source workbooks into my 1 sheet. The selection of the source workbook which depends on the user click the file path in listbox. In the Sheet, i had create the form in the sheet to display the means data and show the selected file path in the range. The vba as shown below:
[VBA]' Show the file path into the range in worksheet
Private Sub ListBox1_Click()
Sheet1.Range("C15").Value = Me.ListBox1.List(Me.ListBox1.ListIndex)
End Sub

Private Sub cmdResult_Click()
Dim Tgt As Worksheet
Dim Source As Range
Dim wbSource As Workbook
Dim cel As Range
Dim Rng As Range
Dim c As Range

Application.ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open(Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex))
Set Source = wbSource.Sheets(1).Columns(1)
With Tgt
.Activate
'clear old data
Range(.Cells(8, 2), .Cells(12, 5)).ClearContents

' Change the name to obey the data structure
If Range("a8").Value = "001" Then
Range("a8").Value = "Staff 001"
End If
If Range("a9").Value = "002" Then
Range("a9").Value = "Staff 002"
End If
If Range("a10").Value = "003" Then
Range("a10").Value = "Staff 003"
End If
If Range("a11").Value = "004" Then
Range("a11").Value = "Staff 004"
End If
If Range("a12").Value = "005" Then
Range("a12").Value = "Staff 005"
End If

'Loop through names in column A
For Each cel In Range("A8:A12")
If Not cel = "" Then
Set c = Source.Range("A3")
Set Rng = Nothing
Do While c.Row < Source.Range("A" & Source.Rows.Count).End(xlUp).Row
If c = cel Then
If Rng Is Nothing Then Set Rng = c.Offset(1)
Set Rng = Union(Rng, Range(c.Offset(1), c.Offset(1).End(xlDown)))
Set c = c.Offset(1).End(xlDown).Offset(1)
Else
Set c = c.Offset(1)
End If
Loop
cel.Offset(, 1) = Application.Average(Rng.Offset(, 1))
cel.Offset(, 2) = Application.Average(Rng.Offset(, 2))
cel.Offset(, 3) = Application.Average(Rng.Offset(, 3))
cel.Offset(, 4) = Application.Average(Rng.Offset(, 4))
End If
Next
End With
' Refill the original name into range
Range("a8").Value = "001"
Range("a9").Value = "002"
Range("a10").Value = "003"
Range("a11").Value = "004"
Range("a12").Value = "005"

wbSource.Close False
Application.ScreenUpdating = True
End Sub[/VBA]
But now, my boss said that it should be transfer the data from the multiple source workbooks into 1 sheet. It means 1 form to display data from 1 source workbooks. I create the many same type form into 1 worksheet and it has the space row between the 2 forms. He want the result as show below.

And I attachd my workbooks here.
Attachment 6289
Thanks for all help!!

[Ann_BBO]

Attachment 6290
This "Staff record 3" workbook is one of the source.workbook. All source workbook which depends on this format.

[Norie]

So what's the actual question/problem?

[Ann_BBO]

Sorry , My english is not good. I repeat my question.
My problem is how to transfer the data of multiple source workbooks to the worksheet. In the worksheet, i create the standard "Form" or table in excel which is display the data from the source workbook. 1 "Form" refer to 1 source workbook in the worksheet.
I had done vba which can be 1 "Form" to display the data from 1 source workbooks into 1 worksheet before. However, i need to do this such as (30 "Form" to display the corrsponding data from 30 source workbook into the same worksheet.)

Now, i don't know how to modify my vba to meet the above observation.

Thanks again

[Ann_BBO]

For all, Do you know what i writing about as my english is not good. Can anyone solve my problems. Or if you want more details, please let me know. Thank you very much!!

[ndendrinos]

Hello Ann
I give you here an example I use to transfer data from multiple workbooks on one sheet.
The multiple workbooks must be all in the same folder (I called mine "Vault")
Download the attachment to your desktop open it and retrieve the folder "Vault" and the Workbook "test"and leave them on your desktop.
Open the WB "test"
In the VB editor you have to edit this line:
[vba]Path = "C:\Documents and Settings\nick dendrinos\Desktop\Vault" 'Change as needed[/vba] to whatever your setting is.
To get the setting do this:
Right click on the folder "Vault" and choose properties.
it should be very similar to my setting with the difference of "nick dendrinos" in mine.
After you edit it should look like this maybe:
[vba]Path = "C:\Documents and Settings\Ann\Desktop\Vault" 'Change as needed[/vba] Now close the VB editor and click on the yellow circle and the data should transfer from all worbooks to this sheet.
When you want to repeat the process you will have to delete the new sheet called "Master" before you run the code again

If this is what you need then post what changes are needed to solve your situation and the masters here will help you.
Hope this helps.

[Ann_BBO]

Thanks ndendrinos!!
Your example is a good concept to me. But, i need to solve the first problem which is import files. For your example, you prefer all source multiple workbooks must be all in the same folder.
[VBA]Path = "C:\Documents and Settings\nick dendrinos\Desktop\Vault" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
Wkb.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Wkb.Close False
FileName = Dir()
Loop[/VBA]
To me, i will input all source.workbooks in the listbox for user select. The problem is It only extract the 1 Workbooks data at once. I don't know how to extract the multi-workbooks data in listbox at once.
[VBA]Set wbSource = Workbooks.Open(Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex))[/VBA]

Anyway, thanks your help.

[Ann_BBO]

For all
Have any idea for transfer data from multiple workbooks on one sheet. I want to stimulate my idea from your support. Thank you