How do I copy and paste multiple worksheets?
Let’s assume you have many worksheets, all in the same structure. Or they are at least in a similar structure. Now, you want to combine them into one worksheet. For example copying them underneath each other so that you can conduct lookups or insert PivotTables. In this article, you learn four methods to merge sheets in Excel. Contents Method 1: Copy and paste worksheets manuallyIn many cases it’s probably the fastest way to just copy and paste each sheet separately. That depends of course on the number of worksheets you want to combine and their structure. Some comments:
Method 2: Use the INDIRECT formula to merge sheetsYou can use Excel formulas to combine data from all worksheets. The main formula is INDIRECT. This method has some disadvantages, though.
On the other hand, it has one major advantage: If you spend effort to set it up, this method is dynamic. That means when your input updates, the merged worksheet updates as well. ApproachSet up the INDIRECT formula for merging sheets.The INDIRECT formula can access any cell from a link (or better: an address) you provide. Please refer to this article to learn more about the INDIRECT formula. So you only have to provide the addresses for each cell in each worksheet you want to combine. Therefore, you should prepare a worksheet the following way (please refer to the screenshot on the right-hand side):
So let’s assume that you want to get the value from cell A1 of Sheet1. You would need then all the parts ‘Sheet1’, column ‘A’ and row ‘1’. Combining them in the INDIRECT formula would lead to the following formula. The formula in cell C4 is =INDIRECT(“‘”&$A4&”‘!”&C$2&$B4) . DownloadCopy this worksheet into your Excel file and all sheets are merged automatically.You want to save some time? We prepared a worksheet which can merge sheets automatically. What do you have to do? Download this workbook (~7 MB) and copy the only sheet into your own workbook. That’s it. Please note the following comments.
Method 3: Merge sheets with a VBA MacroYou feel confident enough to use a simple VBA macro? Please insert the following code into a new VBA module. If you need assistance with VBA, please refer to this article.
Method 4: Combine sheets with “Professor Excel Tools”You like to use the most convenient way? Try the Excel add-in Professor Excel Tools.
Alternatively, you can further refine your desired settings: Do you want to add the original sheet name in column A? No problem. Also, define the copy & paste mode as shown in the screenshot on the right-hand side. This function is included in our Excel Add-In ‘Professor Excel Tools’ (No sign-up, download starts directly) (New) Method 5: Merge sheets using the Office clipboardThe first method above already dealt with copying and pasting sheets manually. There is one more trick here: Use the Excel clipboard to merge sheets. It’s actually quite simple, just follow these steps.
That’s it. Especially with larger files, this method could save some time compared to method number 1 above. One small disadvantage: You can further adjust the pasting method, for example using paste special to paste values only. Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports. Post navigation |