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.

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

"Astongizmo" <(E-Mail Removed)> wrote in message
news82930B8-6299-4F79-AE52-(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news82930B8-6299-4F79-AE52-(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news82930B8-6299-4F79-AE52-(E-Mail Removed)...
>> 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.
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
> news82930B8-6299-4F79-AE52-(E-Mail Removed)...
> > 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.
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news82930B8-6299-4F79-AE52-(E-Mail Removed)...
> >> 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.
> >>
> >>
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> news82930B8-6299-4F79-AE52-(E-Mail Removed)...
> > 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.
> >
> >
>
>
>

HI AStongizmo

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

Have to work now

Best N10
"Astongizmo" <(E-Mail Removed)> wrote in message
news:8145B3FC-73FB-400E-A793-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news82930B8-6299-4F79-AE52-(E-Mail Removed)...
>> > 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.
>> >
>> >
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:3D5A975B-A4BA-42DA-B914-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news82930B8-6299-4F79-AE52-(E-Mail Removed)...
>> > 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.
>> >
>> >
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:8145B3FC-73FB-400E-A793-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news82930B8-6299-4F79-AE52-(E-Mail Removed)...
> >> > 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.
> >> >
> >> >
> >>
> >>
> >>
>
>
>