Consolidate 5 tabs into summary tab

[lfaust]

I have a spreadsheet with 5 tabs in the same format, maintained by different groups. Each tab has rows of task names and an assigned person, among other fields. We need a summary tab. We use Microsoft Teams, so I can't use pivot tables, as they are incompatible with shared edit/downloads. But filters on the summary work fine.

Quick and dirty solution - Manually make summary by typing in the 2 cell references for the first row of each sheet, paste down for enough rows, change cells to absolute references. Add filters. This was a quick fix, but it doesn't cope well when the teams add lines. I need to manually update again.

Sheet 1
Task1, Person1
Task2, Person2

Sheet 3
Task3, Person2
Task4, Person3

Summary Sheet
Task1, Person1
Task2, Person2
Task3, Person2
Task4, Person3

1. Is there a better way to copy the data to summary so it stays updated as people add/remove/update lines in their own tabs
2. Is there a quick way to convert a range of cells to an absolute reference other that using replace function to add $?

 

[mumps]

Does the "Summary" sheet already exist or does it have to be created? Do you want to copy the data from all 5 sheets. Are there any other sheets in the workbook other than the "Summary" sheet and the 5 sheets with data. Does the data in the 5 sheets start in row 1 or do the sheets have headers with the data starting in row 2? What range of cells do you want to convert to absolute and in which sheet(s)?

 

[sandy666]

Use Power Query and Append feature
or post more representative example

 

[lfaust]

@mumps
Does the "Summary" sheet already exist or does it have to be created? It exists only from my quick manual solution, this page could be changed, or a new one could be created.
Do you want to copy the data from all 5 sheets. Are there any other sheets in the workbook other than the "Summary" sheet and the 5 sheets with data. There is one unrelated sheet, could be more in the future.
Does the data in the 5 sheets start in row 1 or do the sheets have headers with the data starting in row 2? They all start in Row 2, after a header.
What range of cells do you want to convert to absolute and in which sheet(s)? This question was more to simplify the manual operation if I have to keep doing it that way. Right now, I go into summary, and make the Cell A2 = Sheet1!B2, and cell B2= Sheet1!I2. copy and drag the 2 cells down until I'm sure I have all the data from Sheet 1, then do the same to add data for Sheets 2-5. Then I am converting the relative references to absolute references, by replacing $b with $b$ and the same thing with $i to $i$. Then users can use sorts and filters on the Summary data. People can update the source rows and the summary is updated. But it doesn't handle new rows correctly, of course.

@sandy666
off to look at power query....

Thanks to both of you,

 

[mumps]

If there could be more sheets in the future, I would need to know the names of those 5 sheets.

 

[lfaust]

The sheet names to be consolidated are:
GL-Features
GL-Data
GL-FrontEnd
GL-Core
GL-ServicesPlatform

The extra sheet is named
common

Current summary sheet is named
Architect

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Architect").UsedRange.ClearContents
    For Each ws In Sheets(Array("GL-Features", "GL -Data", "GL -FrontEnd", "GL -Core", "GL -ServicesPlatform"))
        ws.UsedRange.Offset(1, 0).Copy Sheets("Architect").Cells(Sheets("Architect").Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[lfaust]

1. I've gotten the code to run. I get run time error 91, Object variable or With block variable not set". on the LastRow = Cells.Find..... line Do I need something at the end of each sheet to identify the bottom? Should I replace "*" with this value?
2. I only need columns B and I from each worksheet. I don't see(or perhaps understand) code that would do this - does it currently copy entire line?

I have a small sample file, but can't upload it here. Maybe you can access this dropbox link if you want to see. Sample.xlsm

Thank you so much!

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets("Architects2").UsedRange.ClearContents
    For Each ws In Sheets(Array("GL-Features", "GL-Data", "GL-FrontEnd", "GL-Core", "GL-ServicesPlatform"))
        ws.UsedRange.Offset(1, 0).Copy Sheets("Architects2").Cells(Sheets("Architects2").Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
    Application.ScreenUpdating = True
End Sub