Solved: how to condense results from multiple sheets

[edM]

Hi guys,

This should be simple but i cant figure how to do it!

I have a module which runs which finds 4 excel files (or more) in a specficied directories. It then prints the absolute file names onto sheet1 of the excel file where i run the macro from. OK that works fine

The sub prodcedure below copies cell 1,1 from the excel files and prints each result onto '1 A' on a new sheet in the work book each time called "Sheet_File_x".

So at the minute, if it finds 4 excel files, then 4 extra sheets are created in the workbook.

How could i modify it to just copy all the results to one sheet, one below the other?

I actually dont see where the a new sheet gets created each time it goes around the loop?

Public Sub openFiles()

Set IndexSheet = ThisWorkbook.ActiveSheet
For i = 1 To excelFiles.Count
Dim xl As New Excel.Application
Dim xlw As Excel.Workbook

Dim Filename As String
Filename = excelFiles.Item(i)

Set xlw = xl.Workbooks.Open(Filename)

xlw.Sheets("Sheet1").Activate
Dim copyValue As String
copyValue = xlw.Application.Cells(1, 1).Value

Sheets.Add.Name = "Sheet_File_" & i
xlw.Sheets("Sheet1").Activate
ActiveCell.Activate
ActiveCell.Value = copyValue

Next i

End Sub

thanks for any help!

[Bob Phillips]

Public Sub openFiles()
Dim xl As New Excel.Application
Dim xlw As Excel.Workbook
Dim copyValue As String

Set indexSheet = ThisWorkbook.ActiveSheet
For i = 1 To excelFiles.Count

Dim Filename As String
Filename = excelFiles.Item(i)

Set xlw = xl.Workbooks.Open(Filename)

indexSheet.Range("A" & i).Value = xlw.Worksheets(1).Range("A1").Value
xlw.Close SaveChanges:=False
Next i

End Sub

[edM]

thank you!

[mdmackillop]

Hi Ed
If your question is Solved, please mark it so using the Tread Tools dropdown.