aggregate Data and create new dashboard

[Oryos]

hi everyone,

I'm kinda stuck with my dashboard and I hope you can help me out.
I've a create a dashboard based on data from a kind of log file of software bugs. I've attached the file that describes the situation with the outcome in worksheet Table2.

dashboard.xlsm

The dashboard is a summary of all the log incidents that happened on one day and I already wrote a vba code that doesnt really achieve what I want.

Sub test()
Dim rng1 As Range, rng2 As Range, c As Long, b As Long, u As Long, rng3 As Range
Dim find As Date
Dim rng As Range
Dim Table2 As Worksheets

With Worksheets("Table2")

Set rng = Range("A1")
    For c = 1 To Sheets("Table1").Range("B" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Table1").Range("B" & c)
            For b = 1 To Sheets("Table2").Range("A" & Rows.Count).End(xlUp).Row
                Set rng2 = Sheets("Table2").Range("A" & b)
                If rng1 = rng Then
                 rng2.Offset(0, 2).Value = rng1.Offset(0, 2).Value
            End If
            Set rng2 = Nothing
        Next b
        Set rng1 = Nothing
    Next c
    End With
End Sub

What i'm basically challenged with are:
1. how do I create a table in the middle of a worksheet? My macro starts from the top of the sheet.
2. how can I merge cells so that they look like in my table?
3. my code loops through the all the cells and overwrites the results it already found. Example: cell value "bug description B" for the 15.03. is found first und written into Table2 column C but afterwards "bug description A" is found too and "bug description B" is overwritten. How can I avoid that?

Thanks for your effort and your thoughts!

[Oryos]

Hi everyone,

it's been a while since I started this thread an I was working on my macro a litte bit to achieve what I want to do.

This is how it looks right now:

Sub DoFilter()

Dim rCrit2 As Range, rRng1 As Range, rRng2 As Range
Dim myDate As String
Dim rng1 As Range
Dim Rng As Range
Dim iVal As Integer

Set Rng = Sheets("Table2").Range("A1")
myDate = Date

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
 
Set rRng1 = Sheets("Table1").Range("B1:E1")

With rRng1

    .AutoFilter Field:=1, Criteria1:=myDate, Operator:=xlOr
    For c = 1 To Sheets("Table1").Range("B" & Rows.Count).End(xlUp).Row
       Set rng1 = Sheets("Table1").Range("B" & c)
               If rng1 = Rng Then
               Sheets("Table2").Range("C" & c).Value = rng1.Offset(0, 1).Value
            End If
        Set rng1 = Nothing
    Next c
End With
Application.EnableEvents = True

End Sub

With this macro I could filter out the important entries for today. Thats a good thing. However, What I cant figure out is how to tell my macro that the values of column C (headline: bug) and column E (headline: priority) are written in Table2 row 10 downwards. What my macro does now is that it writes the values into C4 and C5 in Table2 worksheet which are the same cell as in Table1. Thats a problem I'm facing and I wonder if you know how I can fix that.

Basically this is what I'm trying to do:

1. find current date for the entries in Table1
2. copy values in column C and E for current into Table2 starting with cell C10 downwards.

Has anybody any idea on how to do the second part?
Please find attached my excel file and my macro inside.

Thank you and happy easter!


dashboard.xlsm

[p45cal]

Attached file has 2 methods, a pivot table being a manual process of choosing the date, and an Advanced filter method being macro based (a one line macro).

[Oryos]

Hi p45cal,

thanks for you reply. I had a look into your macro over easter holidays and I like especially option 1! Thanks a lot. I was amazed to see that this function works even with a one liner.
Could you just explain why the macro doesnt produce an output of the extract area is in some cells?

For example I put the extract in B4 and C4:

Sub Macro5()
    Sheets("Table1").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Table2").Range("G16:G17"), CopyToRange:=Range("B4:C4"), Unique:=False
End Sub

and I get an error message.
Output area in B23 and C23 works flawless.

    Sub Macro5()
        Sheets("Table1").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Table2").Range("G16:G17"), CopyToRange:=Range("B23:C23"), Unique:=False
    End Sub

Why is that?

Thanks for your help! Very much appreciated!

[p45cal]

The error you get is "The extract range has a missing or illegal field name". There should be field names in the CopyToRange and no blank cells; this is what determines which fields are output. So put 'bug' and 'priority' in cells B4 and C4, make sure there's also room for the results below these cells and you should be good to go.