Macro: extract data from multiple shhets and import into destination sheet based on date qualifier

[FM1]

I have a project schedule setup in Excel to help track and monitor individual tasks. I am in need of a macro which will extract all 'late' tasks which are 'incomplete' into a 'Late Tasks' worksheet.

Workbook setup

6 Worksheets: Plan 1, Plan 2, Plan 3, Plan 4 and Plan 5 (same setup) and Late Tasks

Worksheet setup

Column A = Irrelevant (colour only cell)
Column B = "Plan"
Column C = "Workstream"
Column D = "Task ID"
Column E = "Deliverable"
Columns F:I (irrelevant for macro... I think)
Column J = "Baseline_Finish"
Column K = "Finish_Date"
Column L = "Status"

Ideally, I woul need a macro which extracts all 'late tasks', i.e. Finish_Date greater than Baseline_Date which are incomplete, i.e. Status = "Incomplete", which are imported into the 'Late Tasks' worksheet.

I have setup a template but I cant seem to find the attachment option any more. Im assuming the functionality has been taken out. If anyone is interested to see the file, i can upload it to a fileshare website if that makes things easier.

Thanks!

 

[mumps]

Hi FM1. Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim rng As Range
    For Each ws In Sheets
        If ws.Name <> "Late Tasks" Then
            ws.Activate
            LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each rng In Range("K2:K" & LastRow)
                If rng > rng.Offset(0, -1) And rng.Offset(0, 1) = "Incomplete" Then
                    rng.EntireRow.Copy Sheets("Late Tasks").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
            Next rng
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[FM1]

Hey mumps. Thanks so much for going to the effort if writing all that out. You're a legend.

I'll give it a go first thing Monday and report back.

Cheers.

 

[FM1]

I have just tested the macro and it doesnt seem to be working. Im asusming its because my explanation is not great.

I have uploaded the template on to filshare as it will be easier to help with the code if you can actually view the file: FileSwap.com : Enter Password

Password: mrexcel

 

[mumps]

I had a look at your file and I have a couple of questions. Column A in the "Late Tasks" sheet is labeled as 'Plan'. I don't see any columns in the other sheets with the same label. Do you want the 'Plan' column left blank and if not, what would you like copied into that column? Column F in the "Late Tasks" sheet is labeled as '% Complete'. Do you want this column left blank as well or do you want other data copied to it? The other sheets have a column 'Deliverable' but the "Late Tasks" sheet doesn't have it. Do you not want the 'Deliverable' column copied over? Basically, the columns do not match and I need to know which columns you want copied from the first 5 sheets and where they go on the "Late Tasks" sheet.

 

[FM1]

I had a look at your file and I have a couple of questions. Column A in the "Late Tasks" sheet is labeled as 'Plan'. I don't see any columns in the other sheets with the same label. Do you want the 'Plan' column left blank and if not, what would you like copied into that column? Column F in the "Late Tasks" sheet is labeled as '% Complete'. Do you want this column left blank as well or do you want other data copied to it? The other sheets have a column 'Deliverable' but the "Late Tasks" sheet doesn't have it. Do you not want the 'Deliverable' column copied over? Basically, the columns do not match and I need to know which columns you want copied from the first 5 sheets and where they go on the "Late Tasks" sheet.

Apologies for the poor labelling. Ive just realised that the 'Late Tasks' sheet is designed poorly. To answer your questions:

1. Column A in 'Late Tasks' - can be deleted.
2. Column F in 'Late Tasks' - the data from the other worksheets needs to be copied over when populated.
3. Column D in 'Late Tasks' - should be renamed 'Deliverables'. I incorrectly labelled that column.

In short, I need all 'late' and 'incomplete' tasks data from all the columns in all the worksheets to be carried through to the 'Late Tasks' worksheet. I have populated the worksheet 'Plan 1' with dummy data and I have filled in the 'Late Tasks' worksheet with the data that the macro needs to pull through. Hope this is useful:

FileSwap.com : PM Template 1 v2.xlsx download free

Edit - I have only populated the Plan 1 worksheet but the macro needs to extract the data from all existing worksheets.

 

[mumps]

Hi FM1. Try this macro:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim rng As Range
    For Each ws In Sheets
        If ws.Name <> "Late Tasks" Then
            ws.Activate
            LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each rng In Range("K2:K" & LastRow)
                If rng > rng.Offset(0, -1) And rng.Offset(0, 1) = "Incomplete" Then
                    Range("B" & rng.Row & ":L" & rng.Row).Copy
                    Sheets("Late Tasks").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                End If
            Next rng
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[FM1]

That has worked perfectly. Thanks so much for your help man. Very much appreciated.

Just one question which isnt hugely important, but is there anyway to modify the macro to target specific worksheets? My actual model has the same setup, but it has additional worksheets, e.g. Plan 1 (worksheet 1), Plan 1 - Tracker (worksheet 2), Plan 1 - Graphs (worksheet 3).

Is there something I could add to the macro so it targets all the Plan 1/2/3/4/5 - Tracker worksheets specifically?

Thanks again.

 

[mumps]

Do you want to copy from the Plan 1/2/3/4/5 - Tracker worksheets only and ignore all the others? What about the Plan 1 - Graphs sheet? Do you still want to copy from the Plan 1/2/3/4/5 sheets? Sorry for all the questions but VBA is very picky!

 

[FM1]

Do you want to copy from the Plan 1/2/3/4/5 - Tracker worksheets only and ignore all the others? What about the Plan 1 - Graphs sheet? Do you still want to copy from the Plan 1/2/3/4/5 sheets? Sorry for all the questions but VBA is very picky!

Sorry I made a mistake in my previous post. I want to copy from the 'Plan 1/2/3/4/5 - Schedule' worksheets only and ignore all others.

The setup is as follows: Plan 1 - Schedule (worksheet 1) / Plan 1 - Tracker (worksheet 2) / Plan 1 - Graphs (worksheet 3). This is repeated across Plans 1 - 5.

SO I only need data from Plan - 1 Schedule, Plan 2 - Schedule, Plan 3 - Schedule, Plan 4 - Schedule, Plan 5 - Schedule only. The rest of the worksheets can be ignored.

And please do not apologise. I should be apologising to you for not being clear with my requests and continiously changing the scenario. And thank you again for your help with this.