Copy Project Data to List by criteria

[Stefan_Sand]

hi all,

i have a workbook with a lot of project entries in the worksheet "Project Summary" - whenever there is a new project, there should be a copy entry in the
worksheets small projects or projects if the projects met the criteria "S" or "P" in column C in "Project Summary" - if the data of a certain project in "Project Summary" is changed, the former entries in the two worksheets ("Small Projects" and "Projects") should be erased. How can this question be solved?
any help would be appreciated,
Stefan

[HansV]

Why not simply filter the Project Summary sheet using AutoFilter or Advanced Filter? That way you can be certain that the lists are always up-to-date.

[Stefan_Sand]

well Hans, Your answer was my answer, but the Managers wanted to get a splitted copy list (automatically) so the Managers for the small projects can work with their data seperate to each other....

[HansV]

Right click the sheet tab of Project Summary.
Select View Code from the popup menu.
Copy the following event procedure into the sheet module:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim m As Long
  Dim rng As Range
  m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set rng = Range("A2:E" & m)
  If Not Intersect(Target, rng) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("H3") = "S"
    rng.AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Range("H2:H3"), _
      CopyToRange:=Worksheets("Small Projects").Range("A2:E2")
    Range("H3") = "P"
    rng.AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=Range("H2:H3"), _
      CopyToRange:=Worksheets("Projects").Range("A2:E2")
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
  Range("H3").ClearContents
End Sub

[Stefan_Sand]

ok, the vba is starting, when i enter data, but nothing happens, shall i alter the reference of h3 in your code?


the list in summary projects should stay the same, instead of entries, but the list of projects in the other ws can change.

[HansV]

Sorry, I forgot to mention this:

Enter the word Typ in cell H2.

[Stefan_Sand]

Thank You, it works fine right now.
You are working with the advanced filter? hm, interresting idea.

Stefan

[HansV]

You are working with the advanced filter?

The advantage of Advanced Filter is that it overwrites the copy-to range each time, so you don't have to worry about what has been added, deleted or edited - it's all taken care of automatically.