copy data from several sheets into a summary sheet

Dashboard.xlsm

I see you have a puzzle here !!!
mmmm

Not every sheet is a like and you are looking for a nice touch mission !!!

Several questions:
1) You will be running this macro often ?
2) Each itme it is run you need it I guess to delete Summary sheet and build it from scratch with the data available in the individual sheets ?
3) I see that when there is no data you still want the heading to be copied with the title ? pls confirm.
4) Confirm that always heading in individual sheets is ALWAYS at row 10 ? I did not check but you need to confirm.
5) Last but not least you have 2 fields named: Categorization !!! how are we supposed to find that ?? You are making it mission impossible !!!

That's for now.
gowflow

Hi gowflow

1) You will be running this macro often ? - daily
2) Each itme it is run you need it I guess to delete Summary sheet and build it from scratch with the data available in the individual sheets ? - the user will start with a blank 'template' each day but of course if they ran it several times during the day then yes you are correct.
3) I see that when there is no data you still want the heading to be copied with the title ? pls confirm. - Confirmed
4) Confirm that always heading in individual sheets is ALWAYS at row 10 ? I did not check but you need to confirm. - confirmed

Thanks
Regards

Last point 5)

my error there should only be one column Categorization in 'F'

sorry

Also looking at this more closely I see you have a lot of headings in summary sheet that does not exist in the individual sheets !!! are they going to be blank ??

Are you open in changing the format of Summary sheet to only have 1 heading and then freeze pane so the heading will be locked and all under is the data per sheet ??? like

Row1 Heading --> SheetName Type of   CCY  Amount   Product   Reason   Categorization   Raised on DES  Ageing Site      Issue Owner In Ops
Row2 Data from first sheet if data there
Row3 data from second sheet if data there
etc...

This way it is more compact and easier to see. If data too long you keep scrolling but the header in Row1 still fix and will always show.

gowflow

Hi gowflow

happy to have just the one heading however the user wants to see the name from each sheet even if there is no data like

Dummy

Sellement A

Settlement B

and the reason for this is i) to identify which sheet data is from and ii) to give a comfortable feeling that the Macro has looked at each sheet and returned what was on the sheet [or nothing if sheet has no data]

Regards

ok will do. I will be back if need more info.
gowflow

heheh !!! you have the VBA password protected !!!
gowlfow

I cannot open VBA !! I need the password if you want a code and a solution !! should not post workbooks with password as it is a total waste of time.
gowflow

Very sorry my mistake
Dashboard.xlsm

ok here is the code and the attached workbook. I kept your original Summary under Summary (original) the workbook has the code simply activate the macro CreateSummary and check the results.

Sub UpdateSummary()
Dim WSS As Worksheet
Dim WS As Worksheet
Dim MaxRow As Long, MaxRowS As Long, I As Long, J As Long
Dim cCell As Range
Dim vHeader As Variant

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WSS = Sheets("Summary")
WSS.Cells.Delete
vHeader = Array("Type of", "CCY", "Amount", "Product", "Reason", "Categorization", "Raised on DES", "Ageing", "Site", "Issue Owner In Ops")
WSS.Range("B1:K1") = vHeader
WSS.Range("A1") = "Sheet"
WSS.Range("A1:K1").Font.Bold = True
WSS.Range("A1:K1").Font.Size = 14
WSS.Activate
WSS.Range("A2").Select
Application.ActiveWindow.FreezePanes = True
MaxRowS = 2

'---> Start Process
For Each WS In ActiveWorkbook.Worksheets
    If InStr(1, LCase(WS.Name), "summary") = 0 Then
        MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
        If MaxRow <= 10 Then
            '---> No data in the sheet simply update the Sheet name and move on
            WSS.Cells(MaxRowS, "A") = WS.Name
            WSS.Cells(MaxRowS, "A").Font.ColorIndex = 3
            MaxRowS = MaxRowS + 1
        Else
            '---> Loop thru all data rows of the sheet
            For I = 11 To MaxRow
                WSS.Cells(MaxRowS, "A") = WS.Name
                WSS.Cells(MaxRowS, "A").Font.ColorIndex = 3
                '---> loop thru all fields and get data
                For J = LBound(vHeader) To UBound(vHeader)
                    Set cCell = WS.Range("10:10").Find(what:=Trim(LCase(vHeader(J))), LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
                    If Not cCell Is Nothing Then
                        WSS.Cells(MaxRowS, J + 2) = WS.Cells(I, cCell.Column).Value
                    End If
                Next J
                MaxRowS = MaxRowS + 1
            Next I
        End If
        
        '---> Skip a line between each sheet
        'MaxRowS = MaxRowS + 1
        WSS.Range("A" & MaxRowS & ":K" & MaxRowS).Borders(xlEdgeTop).Weight = xlThin
    End If
Next WS

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

WSS.Range("A1:K1").EntireColumn.AutoFit
MsgBox "Summary sheet Updated.", vbExclamation
End Sub

Select allOpen in new window

gowflow
Dashboard-V01.xlsm

Hi gowflow

That is absolutely brilliant. I just have one question because I know how people make changes once I give them files.

If they were to add additional sheets into the file can I exclude these from being picked up. I guess I would need a piece of code that says exclude these sheets [abc, xyz, etc]

Many thanks for the work you have done on this.

Regards

well very easy, we need to agree on an acronym like I exclude already everything that start with Summary this is why you have summary original and it is not picked up.

So decide on an acronym (any word) and ask your users to always use that word as a prefix to the sheet they create like
USER-Documents
USER-Calendar
etc..
this way will exclude all the user ...

or we can do it reverse like we set all these sheets to have say
SYS-Dummy
SYS-Settlement A
etc.. and will put that the macro will only process the SYS ones

your choice.
I prefer the second as more safe. You hardly can count on users !!!

gowflow

Hi gowflow

I have spoken with my users and they would like the sheets that will not be included in the summary sheet to precede with Mena- like

Mena-Documents
Mena-Calendar

can you help me out with this. Many thanks

that is just brilliant many thanks gowflow. I will leave you alone now to have a rest

just brilliant

Thank you and welcome. Any more help with any question just send me a msg and will be glad to assist.
gowflow

hi gowflow. I have just posted a new question which relates to the work you have already done for me on this one. The user has now asked for certain items [rows] to be copied to a new sheet. I tried Advanced filter but it is too flakey. The link to my new question is :

https://www.experts-exchange.com/questions/28631658/Find-and-then-copy-entire-row-to-new-sheet.html

Regards