VBA to copying a range of data from multiple sheets into a summary sheet (but with some complexity that is giving me a headache)!

[8daveyboy8]

Good morning everyone - hope you're looking forward to the weekend. If any of you have a moment to help, I would be deeply grateful.

Not being a VBA expert, and attempting to steal code from the internet has only left me in a greater pickle! I will attempt to describe the spreadsheet I have and the task I am looking for VBA to perform. But if there is anything that is unclear, please do not hesitate to ask.

Overview:
I am attempting to take data from certain sheets in a workbook. The sheets basically contain a list of actions in rows, and corresponding dates and statuses (being outstanding, in progress, complete) in separate columns across the same row, and I am looking to consolidate all this data into one summary sheet. I will then filter this summary sheet by status so I can see clearly what has been done, or what has not been done as the case may be!

The Data:
The data of tasks and statuses is split across 10 sheets, named ("A1 - PPE", "A2 - Investmt", "A3 - C Assets", "A4 - C Liabilities", "A5 - Employee", "A6 - Tax", "A7 - Legal", "A8 - Contracts", "A9 - Finance", and "A10 - Records"). There are other sheets in the workbook, but these are the only ones that I want to consolidate data from.

The data in these sheets starts at A:7 to column I (Note: the rows above Row 7, are column titles etc). Each sheet has a different numbers of rows/tasks, and I would like the functionality such that I can add in new rows (i.e. add a new actions and statuses etc) into the sheet, and for the VBA code to automatically pick up these new rows and include them in the summary sheet.

The Summary Sheet:
I would like the summary sheet (named "Dashboard") to look similar to the underlying data sheets, and for the imported data to start at A:7 to column I (as I will keeping the same column titles above it as in the rest of the workbook).

Is it possible also to set the code up so it refreshes the summary sheet each time you open the spreadsheet?

Hopefully that is clear - I've tried my best but as with anything that is in my head, it is often difficult to put down in black and white!

Many thanks in advance - Dave

 

[igold]

Hi Dave,

Welcome to the MrExcel Forum.

See if this does what you require. This code will clear & rewrite the "Dashboard" sheet with the information in the named sheets every time you open the workbook. Be Sure to place the code in the "ThisWorkbook" module. It does nothing with any row on any sheet that is less than Row 7. Please test on a backup copy of your data.

Private Sub Workbook_Open()


    Dim x As Long, clRow As Long, sumlRow As Long
    Dim wsNms, aSource
    Dim wsS As Worksheet: Set wsS = Worksheets("Dashboard")
    
    Application.ScreenUpdating = False
    sumlRow = wsS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    wsS.Range("A7:I" & sumlRow).ClearContents
    wsNms = Array("A1 - PPE", "A2 - Investmt", "A3 - C Assets", "A4 - C Liabilities", "A5 - Employee", _
    "A6 - Tax", "A7 - Legal", "A8 - Contracts", "A9 - Finance", "A10 - Records")


    For x = LBound(wsNms) To UBound(wsNms)
        Worksheets(wsNms(x)).Activate
        With ActiveSheet
            clRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            aSource = .Range("A7:I" & clRow)
        End With
        sumlRow = wsS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        wsS.Range("A" & sumlRow).Resize(UBound(aSource, 1), UBound(aSource, 2)).Value = aSource
        aSource = Empty
    Next
    wsS.Select
    MsgBox "Operation Complete"
    Application.ScreenUpdating = True
    
End Sub

I hope this helps...

 

[8daveyboy8]

igold - you're a guddun!

I had a little bit of faffing (mainly because of the clumbsy set up of my spreadsheet), however I have it working a treat now.

Deeply appreciate your time and effort! Many thanks - Dave

 

[igold]

Happy to help. Glad we were able to get it going on the first shot... Thanks for the feedback!