VB Reporting On Late Projects

[MrCrimmy]

Hi All,

I am having a lot of trouble with a particular spreadsheet I'm trying to develop. It's a project tracking tool and what I need to do is copy and past all the late projects into a specific sheet for review with our dept heads which is quite laborious. I am trying to find someway to copy an entire row of cells dependant on one cell and past it into the second work sheet entitled 'Late Projects'.

This is how each project is displayed:


[TABLE="width: 500"]
<TBODY>[TR]
[TD]Project
[/TD]
[TD]Project Subheading
[/TD]
[TD]Assigned
[/TD]
[TD]Created
[/TD]
[TD]Due
[/TD]
[TD]Completed:
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]Project 1
[/TD]
[TD]Project 1A
[/TD]
[TD]John
[/TD]
[TD]01Nov14
[/TD]
[TD]20Nov14
[/TD]
[TD][/TD]
[TD]LATE
[/TD]
[/TR]
</TBODY>[/TABLE]

So effectively I need to scan down through, pick out the lates and copy all the info seen above into a second sheet.

Can anyone provide any help here? I undrestand the basics of VB but I am unsure of how to do this without telling which specific cells to copy and which to paste to. It also doesn't have to use a macro either that was just my approach

I hope that was clear

Thank you

Bryan

 

[onlyadrafter]

Hello,

does this work as expected?

Sub MOVE_LATE_PROJECTS()
    Application.ScreenUpdating = False
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("G" & MY_ROWS).Value = "LATE" Then
            Rows(MY_ROWS).Copy
            With Sheets("Late Projects")
                .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            End With
            Rows(MY_ROWS).Delete
        End If
    Next MY_ROWS
    Application.ScreenUpdating = True
End Sub

change Sheet names as required.

 

[MrCrimmy]

Hi there,

thank you for your reply, unfortunately not, it's not showing any errors but nothing is happening, perhaps i have missed something i was supposed to change. this is what i tried:

Sub MOVE_LATE_PROJECTS()
Application.ScreenUpdating = False
For MY_ROWS = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("H" & MY_ROWS).Value = "late" Then
Rows(MY_ROWS).Copy
With Sheets("late projects")
.Range("B" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
End With
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
Application.ScreenUpdating = True
End Sub

 

[onlyadrafter]

Hello,

Sub MOVE_LATE_PROJECTS()
    Application.ScreenUpdating = False
    For MY_ROWS = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        If UCase(Range("H" & MY_ROWS).Value) = "LATE" Then
            Range("A" & MY_ROWS & ":H" & MY_ROWS).Copy
            With Sheets("late projects")
                .Range("B" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            End With
            Rows(MY_ROWS).Delete
        End If
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Is the source sheet the active sheet when you run the macro?
is there data in col B?
is the late projects sheet name exactly spelt the same?

Have changed the code to copy cols A:H of the specific row (change as required).

You can run through the code line by line if you press F8 when you have the
code window active.

 

[MrCrimmy]

Sorry for my delay in getting back to you, yeah the macro is run in the active sheet, there is data in col B, a better representation of how its laid out can be seen below(The status column is after the three dates, wouldn't fit in the box lol). I have ensured each sheet is named the same, any help you can offer is appreciated:


a b c d e f
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Main Project [/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]Due Date [/TD]
[TD]Completed date[/TD]
[TD]Status[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sub Proj[/TD]
[TD]01/01/14[/TD]
[TD]01/11/14[/TD]
[TD][/TD]
[TD]late [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sub Proj[/TD]
[TD]01/01/14[/TD]
[TD]01/01/15[/TD]
[TD][/TD]
[TD]Not due yet[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sub Proj[/TD]
[TD]01/01/14[/TD]
[TD]01/01/14[/TD]
[TD]01/01/14[/TD]
[TD]Complete[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]