|I'm confused by a couple of your statements:|
"These files only have one sheet containing the information I need"
Does that mean that each file only contains a single worksheet or that each file has multiple sheets but you only need the data from one of the sheets in the file?
"I would need to copy each of those files into a different sheet on the ... (integration file)"
Do you mean that you need to copy the sheet from each file into the integration sheet? You can't copy a file into a sheet since a file is a Windows object. You can however copy a sheet from within an Excel file into another Excel file.
"If it would be possible to rename each sheet in the integration file with the information appearing in cell B4 of each file that would be extra nice"
First, you can't say "cell B4 of each file". B4 is a cell reference of a single cell on a sheet in a workbook. The workbook itself, could be called a "file", but it is incorrect to say that B4 is a cell in a file since a file could contain more than one sheet.
That said, it is possible to rename a sheet based on the value in a cell as long as that cell doesn't contain characters that are not permitted in a sheet name.
For example, if B4 contained 12-27-2011 Data, then that sheet could be renamed based on the contents of the cell. However, if the cell contained 12/27/2011 Data, then the contents could not be used as a sheet name since slashes are considered invalid characters as sheet names.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.