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

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).C opy
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