Then the Do While loop is used to check whether all the files have been covered.The following line assigns the first excel file name to the variable ‘Filename’.The code uses the DIR function to get the file names from the specified folder.This will run the code and all the worksheets from all the Excel files in the folder would get consolidated into a single workbook. Place the cursor anywhere in the code and click on the green play button in the Toolbar options (or press the F5 key).In case you have it in some other location, specify that path here. In the code used above, the folder is on the Desktop. In this line, change the part in double quotes (highlighted in orange) with the location of the folder in which you have the files that you want to combine. In the code, you need to change the following line of code:įolderPath = Environ("userprofile") & " DesktopTest".Copy and paste the above code into the code window.It will open the code window on the right. This will insert a module for the workbook. In the VB Editor, in the Project Editor, right-click on any of the objects for the workbook and go to Insert –> Module.Press ALT + F11 (or go to Developer –> Code –> Visual Basic).For the purpose of this tutorial, I have created a folder named Test and have six files in it (4 Excel workbooks and 1 Power Point and Word each). Put all the Excel files that you want to combine into a folder.Here is the code that can combine multiple Excel workbooks in a specified folder into a single Excel workbook: Sub ConslidateWorkbooks() 'Created by Sumit Bansal from Dim FolderPath As String Dim Filename As String Dim Sheet As Worksheet Application.ScreenUpdating = False FolderPath = Environ("userprofile") & "DesktopTest" Filename = Dir(FolderPath & "*.xls*") Do While Filename "" Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop Application.ScreenUpdating = True End Sub How to Use this Code? However, a simple VBA code can do this in a few seconds.Ĭombine Multiple Excel Files into One File While this can be done manually, it would be time-consuming and error-prone. He had a lot of files in a folder and he wanted to get all the worksheets from all the workbooks into one single workbook. If you’re working on a single monitor, toggle between the two instances of Excel - hover your cursor over the icon in the system tray - to view the multiple sheets in separate windows.I got a call from a friend who wanted to combine multiple Excel files into one Excel workbook. Browse to the first sheet you saved and open it. If you have two monitors, drag the second instance onto it. Keep this file open on your screen.ĭouble-click the Excel icon to open another instance of Excel. Right-click the sheets at the bottom of the workbook and delete all except the sheet to keep and view. Rename the file again with the name of the next sheet to keep and view, such as “Sheet2.” Save the file. Reopen the original Excel workbook, with all of the sheets. Select “Delete.” Repeat until all sheets but one are deleted. Right-click a sheet at the bottom of the workbook that is not the one you want to keep, such as a blank sheet or one with unwanted data. Click the “File” tab and select “Save As.” Rename the file with the name of the single sheet you want to keep, such as “Sheet1.” Choose where to save the file, such as your desktop, and click “Save.” Scroll to the bottom of the screen where the tabbed sheets are viewable. Double-click the Excel icon and click the “File” tab.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |