[RESOLVED] Copying workbooks into a single workbook

[engnouna]

Hi
I have an excel workbook "Statistics" containing 7 worksheets,and 7 workbooks containing data in their first sheet(in these workbooks there are data in Sheet1).I want to copy the content of these 7 workbooks into each sheet in "Statistics"
e.g let's name the 7 workbooks as follow:
Book1,book2,book3,book4,book5,book6,book7 and my workbook "Statistics" contains 7 worksheets:Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6,Sheet7.
I want to copy the content of book1 into Statistics.Sheet1,the content of book2 into Statistics.Sheet2...the content of book7 into Statistics.Sheet7.How can I do it programmatically?
thanks

[Hack]

Moved to Office Development

[DKenny]

Something like this should do it, assuming that the workbooks are all in the same folder. You will need to change the value fo the sPath constant to the folder address.

Sub CopyStats()
Const sPath As String = "C:\data\"
 
Dim lCounter As Long
Dim sBookName As String
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim wksTarget As Worksheet
 
    '7 books to copy
    For lCounter = 1 To 7
        
        'Set the book name
        sBookName = "Book" & CStr(lCounter)
        
        'Open the Workbook
        Set wkbSource = Workbooks.Open(sPath & sBookName)
        
        'Reference the Source Page
        Set wksSource = wkbSource.Worksheets("Sheet1")
        
        'Reference the Destination Page
        Set wksTarget = ThisWorkbook.Worksheets("Sheet" & CStr(lCounter))
        
        'Copy the Source Page to the Destination Page
        wksSource.Cells.Copy wksTarget.Cells
        
        'Clear the Worksheet object variables
        Set wksSource = Nothing
        Set wksTarget = Nothing
        
        'Close the Workbook
        wkbSource.Close False
        
        'Clear the Workbook object variable
        Set wkbSource = Nothing
    Next lCounter
 
End Sub

[engnouna]

Thanks DKenny a lot