Dear VBA for Excel Programmers,

Thank you for your help. Here is a problem that troubles me beyond my present ability to solve. An application that I use exports data in the .xls format. I have about 1000 files in various sub-folders and I need to consolidate all of these into 100 and again down to 10 .xls files so I can analyze the data. Each sub-folder has about (varies) 10 workbook files in it. Each workbook has only one sheet (all named "Sheet1") and the range layout is the same for every file. I want to have one .xlsm file in the root folder and copy the desired range from every .xls file in the sub-folder into a new sheet in the .xlsm that resides in the root folder.

I am able to make a new worksheet named after every file in the sub folder with the below VBA code. I have not figured out how to copy the desired range into the new sheet. I am trying to do it without selecting and activating cells in the source worksheet. How can I copy the cells using variables for workbooks, worksheets and ranges? Your help is greatly appreciated! Thanks in advance.

*******************
Sub test_import()

Dim pth As String
Dim fle As String

' The path to the files
pth = "C:\test folder\"

' Make sheets with same names as files in path
fle = Dir(pth & "*.xls")
Do While Len(fle) > 0

' Add sheet with file name
Sheets.Add.Name = fle

fle = Dir()
Loop

End Sub
*****************

Generate summ rpt,auto send fr various excel files

I found an example that I can modify to do the same as above but I still am not able to get the desired ranges copied into the newly created worksheets.

I leaned enough in the past few hours to solve this problem myself. The wrox p2p Excel 2007 VBA book helped. Here is the simple solution:

Sub test_import()

Dim pth As String
Dim fle As String
Dim pthfle As String
Dim wkb As Workbook

' The path to the files
pth = "C:\your specific folder\"

' Step through each Workbook in subfolder
fle = Dir(pth & "*.xls")
Do While Len(fle) > 0
pthfle = pth & fle

' Add sheet with file name
ThisWorkbook.Sheets.Add.Name = fle
' Open file and get range
Set wkb = Workbooks.Open(Filename:=pthfle, ReadOnly:=True)
wkb.Worksheets("Sheet1").Range("A2:B6").Copy Destination:=ThisWorkbook.Worksheets(fle).Range("A 1:B5")

' Close file after use
wkb.Close SaveChanges:=False

fle = Dir()
Loop

End Sub