AutoFilter and copy
 

I am using Excel 97.

I want to accomplish the following. I want to set a filter then copy
the filtered data to the next available empty cell in the new report.
The code looks something like this.

Dim rngSource As Range, rngDestination As Range

Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=41, Criteria1:="FB"
Set rngSource = ActiveWorkbook.Cells.SpecialCells(xlCellTypeVisibl e).Copy
<- Crashes here on specialcells.
wbNew.Activate
Set rngDestination = ActiveWorkbook.Range(lcCell)
rngSource.Copy rngDestination

The help file says that specialcells returns a range. But I can not
seem to assign the filtered data to a range.

How can I do the above?

AutoFilter and copy
 

Hi Rich

Set rngSource = ActiveWorkbook.Cells.SpecialCells(xlCellTypeVisibl e).Copy

Change to

Set rngSource = ActiveWorkbook.Cells.SpecialCells(xlCellTypeVisibl e)


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rich" wrote in message om...
I am using Excel 97.

I want to accomplish the following. I want to set a filter then copy
the filtered data to the next available empty cell in the new report.
The code looks something like this.

Dim rngSource As Range, rngDestination As Range

Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=41, Criteria1:="FB"
Set rngSource = ActiveWorkbook.Cells.SpecialCells(xlCellTypeVisibl e).Copy
<- Crashes here on specialcells.
wbNew.Activate
Set rngDestination = ActiveWorkbook.Range(lcCell)
rngSource.Copy rngDestination

The help file says that specialcells returns a range. But I can not
seem to assign the filtered data to a range.

How can I do the above?

AutoFilter and copy
 

Change the 'Set rngSource' line to:

Set rngSource = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)


Rich wrote:
I am using Excel 97.

I want to accomplish the following. I want to set a filter then copy
the filtered data to the next available empty cell in the new report.
The code looks something like this.

Dim rngSource As Range, rngDestination As Range

Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=41, Criteria1:="FB"
Set rngSource = ActiveWorkbook.Cells.SpecialCells(xlCellTypeVisibl e).Copy
<- Crashes here on specialcells.
wbNew.Activate
Set rngDestination = ActiveWorkbook.Range(lcCell)
rngSource.Copy rngDestination

The help file says that specialcells returns a range. But I can not
seem to assign the filtered data to a range.

How can I do the above?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html