copy data from several sheets into a summary sheet

Can an expert help me out with this please. I need VBA code that will copy data from each of the sheets into the summary sheet.

The criteria is, Data from the individual sheets needs to be copied to the summary sheet.

On the individual sheets In cell A8 is the name which needs to be copied into the summary sheet followed by the heading [Row 10]  [See summary sheet example]

Under each heading in the summary sheet I need to copy the data from the individual sheets. [again see summary sheet example]

The headings on the summary sheet are:

Type of       CCY      Amount      Product      Reason      Categorization      Raised on DES      Ageing      Site      Issue Owner In Ops      Categorization
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JagwarmanAuthor Commented:
gowflowCommented:
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
JagwarmanAuthor Commented:
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

gowflowCommented:
Last point 5)
JagwarmanAuthor Commented:
my error there should only be one column Categorization in 'F'

sorry
gowflowCommented:
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
JagwarmanAuthor Commented:
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
gowflowCommented:
ok will do. I will be back if need more info.
gowflow
gowflowCommented:
heheh !!! you have the VBA password protected !!!
gowlfow
gowflowCommented:
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
JagwarmanAuthor Commented:
Very sorry my mistake
Dashboard.xlsm
gowflowCommented:
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

Open in new window


gowflow
Dashboard-V01.xlsm
JagwarmanAuthor Commented:
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
gowflowCommented:
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
JagwarmanAuthor Commented:
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
gowflowCommented:
yes sure here it is
gowflow
Dashboard-V02.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
that is just brilliant many thanks gowflow. I will leave you alone now to have a rest
JagwarmanAuthor Commented:
just brilliant
gowflowCommented:
Thank you and welcome. Any more help with any question just send me a msg and will be glad to assist.
gowflow
JagwarmanAuthor Commented:
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 :

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28631658.html

Regards
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.