Consolidation of data sheets onto one worksheet

Question

I have a workbook with about 14 worksheets. A selected set of worksheets contains the data which will be updated. They need to stay separated for the users.

To summarize this data to produce a report, I need to combine all the data sheets (all in the same format) into one for the final summary calculation (so the =AVERAGEIFSfunction could work and some other manually calculated averaging formulae would be more accurate).

I'm thinking of tackling the problem with either of two approaches:

Have the combined datasheet reference each cell on the data sheet individually. My formula is =Sheet1!A1.

The issue is that if any of the rows on the original datasheet gets deleted, it will cause calculation errors on the combined datasheet.

I saw a recommendation of =INDIRECT("Sheet!A1"), but this will not fill correctly across and down the worksheet, meaning I would have to update about 40K cells individually.
A macro or a set of formulae that will pick up data automatically (I'd prefer not to have a running command for the macro).

The design of the macro/formula is to pick up each row from selected worksheets, discontinue once it hits the first blank row and move onto the next selected worksheet, like a loop.

Any other suggestions on how to achieve this would also be highly welcome.

This sounds like something for which VBA would be required. – Tim Williams Sep 09 '16 at 00:58 — Tim Williams, Sep 09 '16 at 00:58

Mauro Costa · Accepted Answer · 2016-09-12T09:09:54.600

Sub combineDatasheets()

Dim sh As Worksheet

For Each sh In Sheets

    If sh.Name <> "Combined datasheet" Then

        a = sh.Cells(1, 1).End(xlDown).Row 'count rows untill blank

        b = Sheets("Combined datasheet").Cells(1, 1).End(xlDown).Row 'last row with data

            'find if there's any data already in "Combined datasheet" by looking at cell A1
            If Sheets("Combined datasheet").Cells(1, 1).Value = "" Then
                b = 0
            End If

        sh.Rows("1:" & a).Copy Destination:=Sheets("Combined datasheet").Range("A" & b + 1)

    End If

Next sh


End Sub

This will get you all the rows with data until the first blank row from each worksheet (ignoring the one where you are consolidating the data, of course) and paste them in the "Combined datasheet" contiguously.

Change the name of the "Combined datasheet" worksheet if necessary.

Note: if the first row is blank, no data will be retrieved from that worksheet.

Hope this helps!

EDIT:

Ok so, if I understood correctly, you want to refresh the data in your consolidated sheet every time a value changes in any other datasheet. So for that use the following code in every worksheet that you want to retrieve data from (the 7 worksheets you mentioned, I guess):

Private Sub Worksheet_Change(ByVal Target As Range)

    Call combineDatasheets

End Sub

Now the following code goes to a module (VBA->Insert->Module):

Sub combineDatasheets()

Dim sh As Worksheet

'Clear data in "Combined datasheet"
c = Sheets("Combined datasheet").Cells(1, 1).End(xlDown).Row
Sheets("Combined datasheet").Range("A1:A" & c).EntireRow.ClearContents


For Each sh In Sheets

    If sh.Name <> "Combined datasheet" Then

        a = sh.Cells(1, 1).End(xlDown).Row 'count rows untill blank

        'fix error when there's only 1 row with data
            If sh.Cells(2, 1).Value = "" Then
                a = 1
            End If


        b = Sheets("Combined datasheet").Cells(1, 1).End(xlDown).Row 'last row with data

            'find if there's any data already in "Combined datasheet" by looking at cell A1
            If Sheets("Combined datasheet").Cells(1, 1).Value = "" Then
                b = 0
                Else
                'fix error when "Combined datasheet" worksheet has only one row with data
                If Sheets("Combined datasheet").Cells(2, 1).Value = "" Then
                    b = 1
                End If
            End If


        sh.Rows("1:" & a).Copy Destination:=Sheets("Combined datasheet").Range("A" & b + 1)

    End If

Next sh

End Sub

Regarding the error you're getting, I think it's because you haven't changed the name of the worksheet that consolidates the information. You need to either change the name to "Combined datasheet" (without quotation marks) so it can work with the code I've written, or you go directly to the code and change the name in there to one of your own choice (every time you see "Combine datasheet" change to the name you want inside the quotation marks).

I hope this will work correctly this time to you :)

Many thanks for your help! Unfortunately I've been getting the Run-time error '9': Subscript out of range error. I'm not too sure what this relates to? My guess is that I haven't given the script information on where to pick up the data - 7 selected worksheets out of the 14 I have on the workbook. Also, this seems to require a manual command to run this script - could this convert to an automated command that this happens each time data on the selected worksheet changes? Many thanks for your help! — kitarika, Sep 12 '16 at 01:13
Thank you very much for the updated script - though there's still some error messages but I found it to be wonderful ideas that I can explore. At this stage, I decided to go with a dumb formula as follows: `=INDIRECT("Sheet1!"&ADDRESS(ROW(), COLUMN()))` - this seems to work fine as long as I program it with the correct row() functions. — kitarika, Sep 14 '16 at 04:03

Consolidation of data sheets onto one worksheet

1 Answers1