Copy and paste row dependant on a cell

[shard]

Hi all, hope some one can help me.

I've been asked to create a spread sheet that lists tasks/jobs for a department for each month.

Each month will have its own worksheet and the details for each task/job will be entered. As each is completed, the last cell in the row (column G)for that particular task will be changed to "completed." That cell is a drop down box and the other option is "Follow Up."

At the end of the month, I want to have a button that is attached to a macro so I can click it and move/copy all the rows that have the end cell value "follow up" to the next month's sheet.

I've had a look with google and a few possibilities seem promising but I've had no success with them as yet.

If anyone could help me it'd be much appreciated.

thanks

-matt

 

[HaHoBe]

Hi, matt,

I would use the autofilter for that (on column G) and copy all visible cells except for the headings.

A macro recording of what I did:

Sub Makro1()
'
' Makro1 Makro
' Makro am 05.08.2009 von HaHoBe aufgezeichnet
'

'
    Selection.AutoFilter
    Selection.AutoFilter Field:=7, Criteria1:="Follow Up"
    Range("A2:G17").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Sheets("August").Select
    Range("A2").Select
    Sheets("Juli").Select
    Selection.Copy
    Sheets("August").Select
    ActiveSheet.Paste
    Sheets("Juli").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
End Sub

A couple of things fixed:

Sub matt()
'
' Makro1 Makro
' Makro am 05.08.2009 von HaHoBe aufgezeichnet
'

'
With ActiveSheet
    If .AutoFilterMode Then .Range("A1:G1").AutoFilter
    .Range("A1:G1").AutoFilter
    .Range("A1:G1").AutoFilter Field:=7, Criteria1:="Follow Up"
    .Range("A2:G" & .Cells(Rows.Count, "G").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
End With
'this is an example to do it at the end of a month, just use the next month´s name
Sheets(Format(DateSerial(Year(Date), Month(Date) + 1, 1), "mmmm")).Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

This code is way off from being what should be used (I know that I should have used Copy with Destination but that may follow).

The selection of the next sheet needs working on as this will always copy to the next mont´s sheet - no matter what sheet you will be on. Maybe advise how your sheets are named for a change here.

Ciao,
Holger