Merging all worksheets from the same workbook

[dy2cakc1]

Hi Guys,

I am not a very experienced person when it comes to excel. I have a workbook with 476 worksheets.

I am using excel 2010 and I want to merge all the data onto one worksheet. The data and the columns etc are the same.

I have no clue about marcos etc. I have tried that consolidating option but doesnt work for me.

I have uploaded the workbook to the following:

https://www.yousendit.com/download/ MFo1QndPZDVRYTljR0E9PQ

I would be extremely grateful if anyone could show me how to do this.

[stanleydgromjr]

dy2cakc1,

Your link is asking for a login.....


To attach your workbook, click on the New Post button, then scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[dy2cakc1]

Hi,

The attachment is 4mb in size which exceeds this forums limit. I have checked the following link and it is valid:

http://www.yousendit.com/download/MF...ZDVRYTljR0E9PQ

[stanleydgromjr]

dy2cakc1,

Got the workbook.

I ran the macro that created a new worksheet Consolidate, that copied the used range from each of the 476 worksheets (some worksheets ran out to columns I, J) (Sheet1 was the only worksheet that had titles), that copied all 21,158 rows of data in 33 seconds on my Lenovo T61 laptop.

It would appear that some of the worksheets have the data in columns Town, Country, Postcode, shifted right. If we tested for the last column of J, and we shifted the last three columns to the right, some data may be lost.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ConsolidateAllSheets macro.