Thanks, that helped. I didn't see all of the options you mentioned in Office
2007, but was able to use autofilter in the macro recorder and it works great.

Here's my code:

Sub vac_filter()
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$65536").AutoFilter Field:=1,
Criteria1:="vacation"
Cells.Select
Sheets("Exec Dir Details").Select
Sheets("Exec Dir Details").Copy After:=Sheets(3)
Range("A1").Select
End Sub

"Don Guillett" wrote:


Have a look at
datafilterautofilterfilter copy/paste. Record
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mary" wrote in message
...
I'm new to Excel macros and need to copy the entire rows for the range
from
the first through the last occurrence of "vacation" in column A. The
number
of rows will vary each month. Then I need to paste those rows onto Sheet1
in
cell A1.

I've tried using the macro recorder but not getting anywhere. Can you
help?

Thanks, Mary