How do I copy an autofilter using a macro?
 

Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second sheet,
so each time I select a new filter the result will be copied to the second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Have you considered using Advanced filter to do this? Just remember to start
on the destination page.

"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

In addition to this excellent idea, if you wish not to copy the Heading Row,
just offset by 1.

Sub Demo()
Dim rngTo As Range

Set rngTo = Sheets(2).Range("A1")

ActiveSheet.AutoFilter.Range. _
Offset(1).Copy rngTo
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"N10" wrote in message
...
Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Thanks for taking the time to respond. I've never used code before either, so
how do I do the first bit "With code select the filtered range"?

"N10" wrote:

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi Dana,

Please see my response to N10 - really appreciate your input.

I am a virgin code user so does your section go before or after the code
suggested by N10?

Thanks once again.

"Dana DeLouis" wrote:

In addition to this excellent idea, if you wish not to copy the Heading Row,
just offset by 1.

Sub Demo()
Dim rngTo As Range

Set rngTo = Sheets(2).Range("A1")

ActiveSheet.AutoFilter.Range. _
Offset(1).Copy rngTo
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"N10" wrote in message
...
Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi Kathy,

Thanks for taking the time out to reply. I'm doing this work for somebody
else and unfortunately they are relatively inexperienced with Excel. For this
reason and tyhis reason alone we are tryinging to automate the process as
much as possible. Advanced autofilter will blow the guys mind.

Have a nice day.

"Kathy" wrote:

Have you considered using Advanced filter to do this? Just remember to start
on the destination page.

"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the second
sheet below any data already there. As a second stage I want to sort the
second sheet and extract all rows where a certain column matches a fixed
criteria into a further sheet. This second stage will require to be done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

HI AStongizmo

Ill write a demo for you latter today which includes the suggestions made
by Dana

Have to work now

Best N10 :)
"Astongizmo" wrote in message
...
Thanks for taking the time to respond. I've never used code before either,
so
how do I do the first bit "With code select the filtered range"?

"N10" wrote:

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably
already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to sort
the
second sheet and extract all rows where a certain column matches a
fixed
criteria into a further sheet. This second stage will require to be
done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi

Before ruling out Kathy's suggestion totally, using Advanced Filter with
a button to invoke the required code is a very simple routine that even
some of my very inexperienced Excel clients can handle without any
difficulty.
Simple dropdowns on the Report sheet to make the selections they want,
then one button click and they can see just the data they need, not all
of the data in the main table.

Take a look at Debra Dalgleish's site for instructions on setting up and
using Advanced Filter with macros. There are also a number of
downloadable files with code set up that you can probably modify to suit
your needs.
http://www.contextures.com/xladvfilter02.html
http://www.contextures.com/excelfiles.html

--
Regards

Roger Govier


"Astongizmo" wrote in message
...
Hi Kathy,

Thanks for taking the time out to reply. I'm doing this work for
somebody
else and unfortunately they are relatively inexperienced with Excel.
For this
reason and tyhis reason alone we are tryinging to automate the process
as
much as possible. Advanced autofilter will blow the guys mind.

Have a nice day.

"Kathy" wrote:

Have you considered using Advanced filter to do this? Just remember
to start
on the destination page.

"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably
already
posted.

I have a spreadsheet containing an autofilter. This always returns
the
same
number of columns but the number of rows can vary. I want to copy
the
displayed rows only to a second sheet building up a list on the
second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to
sort the
second sheet and extract all rows where a certain column matches a
fixed
criteria into a further sheet. This second stage will require to be
done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi N10,

Really appreciate your support. Cheers.

"N10" wrote:

HI AStongizmo

Ill write a demo for you latter today which includes the suggestions made
by Dana

Have to work now

Best N10 :)
"Astongizmo" wrote in message
...
Thanks for taking the time to respond. I've never used code before either,
so
how do I do the first bit "With code select the filtered range"?

"N10" wrote:

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably
already
posted.

I have a spreadsheet containing an autofilter. This always returns the
same
number of columns but the number of rows can vary. I want to copy the
displayed rows only to a second sheet building up a list on the second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to sort
the
second sheet and extract all rows where a certain column matches a
fixed
criteria into a further sheet. This second stage will require to be
done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi Atongzimo

Try this

Creat a new work book to try this code out

On worksheet 1 create three headings in cells A1 b1 c1

I used students score and grade to play with

Populate some values under the headings , I used 5000 data sets and the
code worked quickly

On work sheet 2 create the same headings but with no values under them

Open up the visual basic editor and create a new module.

Copy and paste the following code into the new module

Sub filterdemo()

Dim test As Boolean
test = True

Rem check to see autofilter is on
If ActiveSheet.AutoFilterMode = False Then MsgBox (" YOU HAVE NOT ACTIVATED
THE AUTOFILTER ! TRY AGAIN ")
If ActiveSheet.AutoFilterMode = False Then test = False
If ActiveSheet.AutoFilterMode = False Then Range("A1:C1").AutoFilter
If test = False Then Exit Sub

Application.ScreenUpdating = False
Rem COPY SELCTION
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Rem move to sheet 2 Locate table end & append data

Sheets("Sheet2").Select
Range("A2").Select
ActiveWindow.FreezePanes = True
Range(Selection, Cells(1)).Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste
Application.ScreenUpdating = True

Rem locate end of table
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select

Worksheets("sheet1").Application.CutCopyMode = False

End Sub

Return to sheet 1 and place a button on the sheet. Enter design mode, right
click the button and select view code. Type in call Filterdemo and
then exit design mode. Hit the button and see what happens

Alternatively just run the macro from the visual basic tool bar if dont
want to create a button or other means of running the macro.

You will need to manually go back to sheet 1 after each transfer. Note I
have frozen the pane on sheet 2 so you will need to manually scoll to
review your data

The code can be ammended to suite the range which needs filtering on in
your own work book shoul dyou decide to transfer the cosde

Check out the suggestions byother posters and know that what I have
ctreated is pretty basic code , just one way to do what you want do, there
are more elegant and efficient ways

Good luck

n10 :)









"Astongizmo" wrote in message
...
Hi N10,

Really appreciate your support. Cheers.

"N10" wrote:

HI AStongizmo

Ill write a demo for you latter today which includes the suggestions
made
by Dana

Have to work now

Best N10 :)
"Astongizmo" wrote in message
...
Thanks for taking the time to respond. I've never used code before
either,
so
how do I do the first bit "With code select the filtered range"?

"N10" wrote:

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably
already
posted.

I have a spreadsheet containing an autofilter. This always returns
the
same
number of columns but the number of rows can vary. I want to copy
the
displayed rows only to a second sheet building up a list on the
second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to sort
the
second sheet and extract all rows where a certain column matches a
fixed
criteria into a further sheet. This second stage will require to be
done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

Hi N10,

Looks impressive enough to me but when I've tried to run it it comes up with
a Compile error : Syntax error and highlights the following line

If ActiveSheet.AutoFilterMode = False Then MsgBox (" YOU HAVE NOT ACTIVATED
THE AUTOFILTER ! TRY AGAIN ")

Any ideas?

Thanks once again.

"N10" wrote:

Hi Atongzimo

Try this

Creat a new work book to try this code out

On worksheet 1 create three headings in cells A1 b1 c1

I used students score and grade to play with

Populate some values under the headings , I used 5000 data sets and the
code worked quickly

On work sheet 2 create the same headings but with no values under them

Open up the visual basic editor and create a new module.

Copy and paste the following code into the new module

Sub filterdemo()

Dim test As Boolean
test = True

Rem check to see autofilter is on
If ActiveSheet.AutoFilterMode = False Then MsgBox (" YOU HAVE NOT ACTIVATED
THE AUTOFILTER ! TRY AGAIN ")
If ActiveSheet.AutoFilterMode = False Then test = False
If ActiveSheet.AutoFilterMode = False Then Range("A1:C1").AutoFilter
If test = False Then Exit Sub

Application.ScreenUpdating = False
Rem COPY SELCTION
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Rem move to sheet 2 Locate table end & append data

Sheets("Sheet2").Select
Range("A2").Select
ActiveWindow.FreezePanes = True
Range(Selection, Cells(1)).Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste
Application.ScreenUpdating = True

Rem locate end of table
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select

Worksheets("sheet1").Application.CutCopyMode = False

End Sub

Return to sheet 1 and place a button on the sheet. Enter design mode, right
click the button and select view code. Type in call Filterdemo and
then exit design mode. Hit the button and see what happens

Alternatively just run the macro from the visual basic tool bar if dont
want to create a button or other means of running the macro.

You will need to manually go back to sheet 1 after each transfer. Note I
have frozen the pane on sheet 2 so you will need to manually scoll to
review your data

The code can be ammended to suite the range which needs filtering on in
your own work book shoul dyou decide to transfer the cosde

Check out the suggestions byother posters and know that what I have
ctreated is pretty basic code , just one way to do what you want do, there
are more elegant and efficient ways

Good luck

n10 :)









"Astongizmo" wrote in message
...
Hi N10,

Really appreciate your support. Cheers.

"N10" wrote:

HI AStongizmo

Ill write a demo for you latter today which includes the suggestions
made
by Dana

Have to work now

Best N10 :)
"Astongizmo" wrote in message
...
Thanks for taking the time to respond. I've never used code before
either,
so
how do I do the first bit "With code select the filtered range"?

"N10" wrote:

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter criteria

N10 ;)



"Astongizmo" wrote in message
...
Hi - I'm new to macros and the answer to my question is probably
already
posted.

I have a spreadsheet containing an autofilter. This always returns
the
same
number of columns but the number of rows can vary. I want to copy
the
displayed rows only to a second sheet building up a list on the
second
sheet,
so each time I select a new filter the result will be copied to the
second
sheet below any data already there. As a second stage I want to sort
the
second sheet and extract all rows where a certain column matches a
fixed
criteria into a further sheet. This second stage will require to be
done
twice as the criteria changes.

Any help will be gratefully received.

How do I copy an autofilter using a macro?
 

HI AStongizmo

Hi I dont know or cant figure out what is exactly wrong with the line of
code. In the form I wrote it it works perfectly.

In your module does the code you mention below occupy two lines ? if so does
the end of the first line have the "_ " symbol; you masy have inaddveretenly
hit a carrige reutn in the wrong place produce a syntax error.


You can if you like copy the code from the module you created, paste it into
notepad and email it to me. You can get my email ad by the properties of
this post :)


Ive checked the code it and works fine for me. Maybe try deleting your code
and then repaste the following


Sub filterdemo()


Dim test As Boolean
test = True


Rem check to see autofilter is on
If ActiveSheet.AutoFilterMode = False Then MsgBox (" YOU HAVE NOT MADE A
SELECTION TRY AGAIN")
If ActiveSheet.AutoFilterMode = False Then test = False
If ActiveSheet.AutoFilterMode = False Then Range("A1:C1").AutoFilter
If test = False Then Exit Sub

Application.ScreenUpdating = False
Rem COPY SELCTION
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Rem move to sheet 2 Locate table end & append data

Sheets("Sheet2").Select
Range("A2").Select
ActiveWindow.FreezePanes = True
Range(Selection, Cells(1)).Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste
Application.ScreenUpdating = True

Rem locate end of table
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select

Worksheets("sheet1").Application.CutCopyMode = False




Best N10



"Astongizmo" wrote in message
...
Hi N10,

Looks impressive enough to me but when I've tried to run it it comes up
with
a Compile error : Syntax error and highlights the following line

If ActiveSheet.AutoFilterMode = False Then MsgBox (" YOU HAVE NOT
ACTIVATED
THE AUTOFILTER ! TRY AGAIN ")

Any ideas?

Thanks once again.

"N10" wrote:

Hi Atongzimo

Try this

Creat a new work book to try this code out

On worksheet 1 create three headings in cells A1 b1 c1

I used students score and grade to play with

Populate some values under the headings , I used 5000 data sets and the
code worked quickly

On work sheet 2 create the same headings but with no values under them

Open up the visual basic editor and create a new module.

Copy and paste the following code into the new module

Sub filterdemo()

Dim test As Boolean
test = True

Rem check to see autofilter is on
If ActiveSheet.AutoFilterMode = False Then MsgBox (" YOU HAVE NOT
ACTIVATED
THE AUTOFILTER ! TRY AGAIN ")
If ActiveSheet.AutoFilterMode = False Then test = False
If ActiveSheet.AutoFilterMode = False Then Range("A1:C1").AutoFilter
If test = False Then Exit Sub

Application.ScreenUpdating = False
Rem COPY SELCTION
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Rem move to sheet 2 Locate table end & append data

Sheets("Sheet2").Select
Range("A2").Select
ActiveWindow.FreezePanes = True
Range(Selection, Cells(1)).Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste
Application.ScreenUpdating = True

Rem locate end of table
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select

Worksheets("sheet1").Application.CutCopyMode = False

End Sub

Return to sheet 1 and place a button on the sheet. Enter design mode,
right
click the button and select view code. Type in call Filterdemo
and
then exit design mode. Hit the button and see what happens

Alternatively just run the macro from the visual basic tool bar if dont
want to create a button or other means of running the macro.

You will need to manually go back to sheet 1 after each transfer. Note I
have frozen the pane on sheet 2 so you will need to manually scoll to
review your data

The code can be ammended to suite the range which needs filtering on
in
your own work book shoul dyou decide to transfer the cosde

Check out the suggestions byother posters and know that what I have
ctreated is pretty basic code , just one way to do what you want do,
there
are more elegant and efficient ways

Good luck

n10 :)









"Astongizmo" wrote in message
...
Hi N10,

Really appreciate your support. Cheers.

"N10" wrote:

HI AStongizmo

Ill write a demo for you latter today which includes the suggestions
made
by Dana

Have to work now

Best N10 :)
"Astongizmo" wrote in message
...
Thanks for taking the time to respond. I've never used code before
either,
so
how do I do the first bit "With code select the filtered range"?

"N10" wrote:

Hi

Try this


With code select the filtered range

then use

Selection.SpecialCell(xlCellTypeVisible).Select
Selection.Copy

This copies only the cells visble as a result of the filter
criteria

N10 ;)



"Astongizmo" wrote in
message
...
Hi - I'm new to macros and the answer to my question is probably
already
posted.

I have a spreadsheet containing an autofilter. This always
returns
the
same
number of columns but the number of rows can vary. I want to copy
the
displayed rows only to a second sheet building up a list on the
second
sheet,
so each time I select a new filter the result will be copied to
the
second
sheet below any data already there. As a second stage I want to
sort
the
second sheet and extract all rows where a certain column matches
a
fixed
criteria into a further sheet. This second stage will require to
be
done
twice as the criteria changes.

Any help will be gratefully received.