Filter many excel sheets to one

[jhoward0101]

Hello,
I use an excel workbook with 7 worksheets that overall have about 35,000 rows of data. Every 30 seconds, several fields are updated which cause data in the rows to change and recalculate, based on filter variables, some rows appear, and others are hidden.

What I would like to do is filter all 7 of these sheets into one dashboard sheet. As rows unhide or hide, I want the dashboard sheet to show results of all 7 sheets.

Does anyone know how this can be accomplished in excel? Appreciate any assistance that could be offered. I looked into Advanced Filtering, but from what I can tell, only one sheet can be used to filter data to another.
I have some experience with VBA, but prefer to have Excel do it, if possible.
I have written code to parse the sheets and write to one output sheet, but this takes too much time.

On Windows 10, using both Excel 2010 and Excel 2016.

Thanks, in advance.
Jim

 

[Yongle]

Is this what you want?
When should the VBA run? What is the trigger?

VBA below copies current visible rows (excluding header row) from 7 worksheets and pastes values into single sheet
- first column A, last column P, first data row 2 - amend as required
- amend the array of sheet names

Place code in a standard module with constants before all procedures

Const fCol = "[COLOR=#ff0000]A[/COLOR]"
Const lCol = "[COLOR=#ff0000]P[/COLOR]"
Const fRow = [COLOR=#ff0000]2[/COLOR]

Sub ConsolSheets()
    Dim sh
    Call SpeedUp(True)
    For Each sh In Array("shA", "shB", "shC", "shD", "shE","shF","shG")   [COLOR=#ff0000]'amend sheet names[/COLOR]
        GetRange(Sheets(sh)).SpecialCells(xlCellTypeVisible).Copy
        Call PasteVisibleValues
    Next
    Call SpeedUp(False)
End Sub

Private Function GetRange(sh As Worksheet) As Range
       Dim r As Long
       r = sh.Range(fCol & Rows.Count).End(xlUp).Row
       Set GetRange = sh.Range(fCol & fRow & ":" & lCol & r)
End Function

Private Sub PasteVisibleValues()
    With Sheets("[COLOR=#ff0000]Dashboard[/COLOR]").Range(fCol & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial (xlPasteValues)
        .PasteSpecial (xlPasteFormats)
    End With
End Sub

Private Sub SpeedUp(yes As Boolean)
    With Application
        If yes = True Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
        Else
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End If
    End With
End Sub

 

[jhoward0101]

Yongle,
First, thanks so much for your reply and help. it is much appreciated.
I made a minor change to code, I added Sheets("Dashboard").Cells.Clear to start fresh. Code works perfectly.
May I ask for one final change?
I would like to start the paste at row 10 in the dashboard, for the first paste. How would I do that?

 

[Yongle]

I would like to start the paste at row 10 in the dashboard, for the first paste. How would I do that?

VBA looks for last value in column fCol and then puts next value in row below
- so the easiest way would be to put something in the appropriate cell in row 9 if it is empty
- code amended to place a space in the cell if empty and remove it again afterwards

Replace Private Sub PasteVisibleValues with the code below

Private Sub PasteVisibleValues()
    With Sheets("Dashboard")
        With .Range(fCol & 9)
            If .Value = "" Then .Value = " "
        End With
        With .Range(fCol & Rows.Count).End(xlUp).Offset(1)
            .PasteSpecial (xlPasteValues)
            .PasteSpecial (xlPasteFormats)
        End With
        With .Range(fCol & 9)            
            If .Value = " " Then .clearcontents
        End With
    End With
End Sub

 

[jhoward0101]

Once again, many thanks. This has helped me tremendously
Regards