macros, filters, HELP
 

I have searched the web long enough for the answer to my problem and
have finally given up. Maybe someone here can help.

I need to be able to filter data from one page and then copy it to
another page by use of a macro. Sounds simple, I know....but I still
haven't found the correct code. The info will be copied from a page
titled "database" and copied to a sheet titled "search". (a4:i4) and
could grow over time to contain as much as 25000 rows of info. I
don't want to alter the database page in any way, just copy the info
from that page.

Please help

Thanks.........Curtis

macros, filters, HELP
 

This copies the data from an Advanced filter on sheet1
into sheet2. It clears any previous data in sheets2 from
previous filters. Change the references as needed.

Sub copyAdvFilter()
'1/11/03
Dim rng As Range
Dim i As Integer, j As Integer
With Worksheets(1)
Application.ScreenUpdating = False
Range("A10").Select
i = ActiveCell.CurrentRegion.Rows.Count
j = ActiveCell.CurrentRegion.Columns.Count
Set rng = Range(Cells(10, 1), Cells(i + 10, j))
Worksheets("sheet2").Cells.Clear
rng.Select
rng.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:E2"), Unique:=False
rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
ActiveSheet.ShowAllData
Range("A10").Select
Application.ScreenUpdating = True
End With
End Sub

See also Deborah Dalgliesh's site Contextures.com for
excellent tutorials with autoFilters including a program
by Tom Ogilvy to to copy autofiltes to another sheet.

Regards
Peter
-----Original Message-----
I have searched the web long enough for the answer to my
problem and
have finally given up. Maybe someone here can help.

I need to be able to filter data from one page and then
copy it to
another page by use of a macro. Sounds simple, I
know....but I still
haven't found the correct code. The info will be
copied from a page
titled "database" and copied to a sheet
titled "search". (a4:i4) and
could grow over time to contain as much as 25000 rows of
info. I
don't want to alter the database page in any way, just
copy the info
from that page.

Please help

Thanks.........Curtis
.

macros, filters, HELP
 

Hi CaveMan,

The code would be something like this..
You can use "AutoFilter" method and to copy filtered range, can use
"AutoFilter.Range" as follows.


Code:
--------------------

Sub Macro1()
With Sheets("database")
.AutoFilterMode = False 'Just in case
.Range("A1").CurrentRegion.AutoFilter _
Field:=1, _
Criteria1:="something"
With .AutoFilter.Range
'Remove Title row
'If you want to add the Title, just use .Copy _ instead
.Resize(.Rows.Count - 1).Offset(1).Copy _
Sheets("search").Range("A4")
End With
.AutoFilterMode = False
End With
End Sub

--------------------



---
Message posted from http://www.ExcelForum.com/

macros, filters, HELP
 

Just thought I would say THANKS for the help. I was able to make
some changes to the examples and get everything to work just the way I
wanted. Once again, Thanks.......


CaveMan