Merge multiple excel files vba

Cells 1. Cells Rows. Count, 2. End xlUp 2 With. CurrentRegion With. Count — 1. Offset 1. Copy LastR LastR , 0. Close False End With Next ws. AutoFit Application. Select Selection. Select End Sub. Thank for Code.. Its very helpful!! Kindly help on this. Thanks again!!! Thank you for input, It gives me error as Subscript out of Range, Please help to change sheet and also I require only few columns to be selected and copied, Is that possible?

Hi Raghu R. Your recommendations only allows for the data to be written to a specific sheet destination sheet. Are you able to comment on how to copy a specific sheet from the source.

Your site provided us with valuable info to work on. You are a life saver! Is there a way to incorporate the file name of the files we merge as well please? I don't know yet on how to do that, or what tool should I use, like listbox or what. Thanks in advance! I have a code for selecting multiple excel files. What I need now is how to merge the files that I selected. Multi-Select Files and open. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How do I merge multiple selected excel files in VBA? Ask Question. Asked 5 years, 11 months ago. Active 5 years, 11 months ago.

Viewed 2k times. Improve this question. Ze Louise. Ze Louise Ze Louise 1 5 5 bronze badges. First, how you want to merge the files??? Adding all the sheets into a new workbook??? Into an already open workbook??? If the sheets has the same names what you want to do??? All the excel files have only one worksheet and have different names. Btw, by excel files I mean workbooks. Add a comment. As soon as you execute the VBA code, the VBA macro will cycle through each available worksheet in the primary workbook data workbook and paste the contents into a newly added sheet within the same workbook.

Copy-paste this code into the VBA editor's module:. First, declare all the variables you're using within the code and assign them with the correct VBA data types to make the code run seamlessly. Once you declare the variables, some basic housekeeping is needed. This is done by disabling screen flickering and suppressing pop-up alerts. For example, when you delete an existing sheet using the VBA code, a prompt within Excel asks for confirmation before deleting the sheet.

Prompts like this are suppressed to enhance the speed of execution. In the next step, you need to define the workbook's name, which contains all of your data. Replace Test. Make sure you surround the name with quotes. Activate the primary workbook and delete any existing sheets with the name Consolidated to eliminate any previously stored data.

The VBA code toggles through each sheet, and as soon as it encounters the sheet name Consolidated it'll delete it. This is done using the VBA IF statement , which checks for logical conditions and deletes the sheet as soon as the condition is met. A new sheet is added to the primary workbook to store the consolidated data.