CODEWITHSUNDEEP
mergeexcel-2016
David Gard
David Gard

Reputation: 12047

Merge data from many sheets on to one

In excel, I have several sheets (around 50), each with an identical header in columns A, B and C, and then up to 199 rows of data (row 1 = header, rows 2-200 = data). The naming routine is as Wk 1 Mon, Wk 2 Tue, etc, all the way up to Wk 10 Fri

What I would like to do is display all of the data from these tabs in one list, on one sheet. I could potentially do this by referencing each cell from each sheet, one under the other, but the problem is that not all sheets actually have data right the way down to row 200 (some have just the header), and I wish to skip empty rows.

I have absolutely no clue how to approach this in Excel. My understanding of VLOOKUP and the like is rudimentary at best; I'm not sure if I could even achieve what is required by using that family of functions.

I've also looked in to the Consolidation feature of Excel, but I don't think that is what I need in this scenario.

Could someone please suggest how I may achieve my goals. I would prefer to do this via worksheet only functions, but I'd be open to VBA if there was an easy enough solution.

Upvotes: 0

Views: 296

Answers (1)

Amit Patel
Amit Patel

Reputation: 361

Try this bit of VBA. It basically scrolls through each worksheet, finds the last row and pastes it on the bottom of the first sheets data. It's a bit crude in methodology but it does work!

Dim ws As Worksheet

For Each ws In Worksheets
    i = i + 1

    If i = 1 Then
        FirstSheet = ws.Name
    ElseIf i > 1 Then
        ws.Activate
        LastCell = Cells(65536, 1).End(xlUp).Row
        Range("A1:C" & LastCell).Select

        Selection.Copy
        Worksheets(FirstSheet).Activate
        Cells(Cells(65536, 1).End(xlUp).Row + 1, 1).Select
        ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
    End If
Next ws

Upvotes: 1

Related Questions