Merge Excel worksheets?

July 21, 2008 8:23 AM

What's the easiest way to merge data from multiple Excel worksheets into one big worksheet

I have a workbook with 223 worksheets. Each worksheet contains information about a given geographic area. The information for any given variable is in the same cell each time. I would like to convert this into one big worksheet.
---------------
Example of what I have:

Worksheet 1 [Area 1]:
Blue: 3
Black: 5
Green: 4

Worksheet 2 [Area 2]:
Blue: 5
Black: 2
Green: 5
------------------
Example of what I want

ONE BIG WORKSHEET
Area, Blue, Black, Green
1, 3, 5, 4
2, 5, 2, 5
--------------------------

If, for example, the value for 'Blue' will always be in cell B3, then, essentially I want a macro that works like this:

For i < 224
BIGSHEETBi=SHEETi!B!3

posted by ewiar to Computers & Internet (2 answers total)

Best answer: You can pretty much do exactly what you typed there. Note -- I haven't tested this but I think it should work. (Hopefully it posts okay, might be some extra spaces.)

Sub CombineData()
  For i = 1 To Sheets.Count
    Worksheets("BigSheet").Range("B" & i).Value = Worksheets(i).Range("B" & i).Value
  Next
End Sub

posted by inigo2 at 8:40 AM on July 21, 2008

Response by poster: For anyone with the same problem, I also hacked together the following far less elegant solution.

Three columns: A, B, C
In A: 1 through n (A1: 1, A2: "=A1+1", fill down)
In B: " '=Sheet "; fill down
In C: ![Cell coordinates]; fill down

Concatenate the three in a fourth column: "=concatenate(B1, A1, C1)"; fill down

Cut and paste the column with the concatenated formulae (the first will look like "=Sheet1![Cell Coordinates]") into Notepad. Copy and paste from Notepad back into Excel into the desired column.
posted by ewiar at 1:08 PM on July 21, 2008