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.
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 :)