Macro to find range of cells

[Mary]

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

 

[Bernard Liengme]

Please tell us in more detail how the layout of the data
best wishes

 

[Mary]

There are several columns of data separated by function. I want to find and
copy only the rows that have "vacation" in column A to a new worksheet. I
have a macro that sorts the workbook, so all rows are together. The first
and last row for the rows that have the vacation information will vary each
month when the report comes out.

 

[Don Guillett]

Have a look at
data>filter>autofilter>filter >copy/paste. Record

 

[Mary]

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