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.
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()
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.