VBA Help - Using excel as an action tracker

[pauleverton]

Hi,

Any help anyone can give with this would be really appreciated.

I'm trying to create a system to track project actions and updates within excel - can't use MS Access or MS Project otherwise I would, so having to make do!

I've got a separate worksheet for each project and I'm adding all of the actions into one location, so that I can then easily extract them to help create a Gantt view of progress across everything(which is why I've used activesheet in the below, means I don't have to hard-code anything per project). I'm trying to get the macro to update the task if it's already in the list and, if not, add the details in (not fussed if it does this the other way around).

Sub updateactions()

Dim actions As Worksheet
Set actions = Worksheets("Actions")
Dim project As Worksheet
Set project = ActiveSheet


For j = 1 To 200


For i = 1 To 200
    
    If project.Cells(j, 1).Value = actions.Cells(i, 1).Value Then
            actions.Cells(i, 2).Value = project.Cells(j, 2).Value
            actions.Cells(i, 3).Value = project.Cells(j, 3).Value
            actions.Cells(i, 4).Value = project.Cells(j, 4).Value
            actions.Cells(i, 5).Value = project.Cells(j, 5).Value
            actions.Cells(i, 6).Value = project.Cells(j, 6).Value
            actions.Cells(i, 7).Value = project.Cells(j, 7).Value
    End If
    Next
Next


Dim lastrow As Long
lastrow = project.Cells(Rows.Count, "A").End(xlUp).Row + 1
Set Rng = project.Range("A16:A" & lastrow)


For Each c In Rng
   If WorksheetFunction.CountIf(actions.Range("A:A"), c.Value) = 0 Then
     actions.Range("A" & actions.Cells(Rows.Count, 1).End(xlUp).Row)(2) = c.Value
   End If
Next


actions.Range("A2:G" & lastrow).Borders.Weight = xlThin


End Sub

At the moment it's doing ok if the ID already exists, but isn't necessarily adding in all of the new additions and the full property from what it's selected. It's also slow, but I'm not sure how to make it a bit neater so that it only runs through the code once.

I know that Excel isn't really intended to handle lots of bits like this, but it's all I've got to work with, so having to make do! As I've said any help is really appreciated

 

[pauleverton]

Bump if anyone is able to help. Thanks.