Rudolph_C_Mancilla
asked on
Excel 2002 -- Combining multiple worksheets in multiple spreadsheets into a single worksheet.
I have multiple worksheets spread across multiple Excel files (1 worksheet per file). All files are stored in the same folder, and all worksheets have the same column headers and structure. I need a block of code that will combine all of these worksheets into a single worksheet in a master Excel file. That is, the code needs to:
1. Open the first Excel file.
2. Copy the first worksheet's contents into the first worksheet of the master file, beginning at the next empty row it finds.
3. Close the Excel file, and move on to the next file.
4. Repeat.
So in the end, ten worksheets residing on ten different Excel files will be combined into a single worksheet in a single file. No breaks are needed between them, instead, the last row of a worksheet would be followed by the first row of the next one immediately below it. No aggregate functions involved, no sums, nothing like that (which is why I don't think I can use the Consolidate function in Excel). Hope all this detail helps! Thanks in advance for the help!
1. Open the first Excel file.
2. Copy the first worksheet's contents into the first worksheet of the master file, beginning at the next empty row it finds.
3. Close the Excel file, and move on to the next file.
4. Repeat.
So in the end, ten worksheets residing on ten different Excel files will be combined into a single worksheet in a single file. No breaks are needed between them, instead, the last row of a worksheet would be followed by the first row of the next one immediately below it. No aggregate functions involved, no sums, nothing like that (which is why I don't think I can use the Consolidate function in Excel). Hope all this detail helps! Thanks in advance for the help!
ASKER
Only alphanumeric characters: a date column, three number columns, and four text columns. Nothing more.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Warning!
It will work for all excel files in given directory.
Suat
It will work for all excel files in given directory.
Suat
"This code will transfer data within first sheet in the excel files into the ACTIVE workbook."
Please let me know if you need more than this.
Suat
Please let me know if you need more than this.
Suat
Hi,
Could you explain the following statement:
sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).End(xlToRight ))
rng.Copy thissht.Cells(65536, 1).End(xlUp).Offset(1)
Its not working correctly and want to mendle it.
Being new to Excel not aware of the usage of Range...
TCSD
Could you explain the following statement:
sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).End(xlToRight
rng.Copy thissht.Cells(65536, 1).End(xlUp).Offset(1)
Its not working correctly and want to mendle it.
Being new to Excel not aware of the usage of Range...
TCSD
Suat