Merge Excel worksheets?
July 21, 2008 8:23 AM   Subscribe

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

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
posted by ewiar to Computers & Internet (2 answers total) 3 users marked this as a favorite
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
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

« Older More.. a little more... a little m... no, too much...   |   I want a cat -- but should I? But I really want. Newer »
This thread is closed to new comments.