I have several files saved under the same folder (which each has one worksheet only), I would like to single click of a button to combine all these files into one (by entering the location of the folder where all these files are saved), so that I can have just 1 file that contains different worksheets. thanks
is it also possible to extract the name from a single cell from each file (same location on all files), and use that to rename the tab in the combined file?
Re: Combine Several Files Into One
this macro opens every file in a directory and copies a certain sheet to the main workbook:Code
Dim objFso As Object Dim objFolder As Object Dim objFile As Object Dim objFiles As Object Dim strFolder As String Dim strWorkbook As String strWorkbook = "ozz.xls" 'Main workbook 'Create objects Set objFso = CreateObject("Scripting.FileSystemObject") strFolder = "c:\tmp\test" 'Folder directory Set objFolder = objFso.getfolder(strFolder) Set objFiles = objFolder.Files 'Loop through files For Each objFile In objFiles Workbooks.Open strFolder & "\" & objFile.Name 'Copy sheet ActiveWorkbook.Sheets(1).Copy Before:=Workbooks(strWorkbook).Sheets(1) 'Copy sheet Windows(objFile.Name).Activate ActiveWorkbook.Close Next objFile 'Clear memory Set objFile = Nothing Set objFiles = Nothing Set objFolder = Nothing Set objFso = Nothing
Hope this gives you a start.