I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

Assuming a header row in row 1, you can use these two lines of code to select
only visible data starting in row 2:

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason G" wrote:

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

thansk, this helped loads, although it prompted another query, while running
different filters to grab different chunks of data if i manually select the
first empty row in my destination worksheets this is giving me the same
problem when recording my macro, i.e. it's recording a macro with a row
number in it, what's the code equivelent for 'first empty row'?

many thanks.....



"Luke M" wrote:

Assuming a header row in row 1, you can use these two lines of code to select
only visible data starting in row 2:

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason G" wrote:

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

Have a look at the code Ron de Bruin provides for filtering one sheet to
many sheets.

http://www.rondebruin.nl/copy5.htm


Gord Dibben MS Excel MVP

On Tue, 1 Dec 2009 06:16:02 -0800, Jason G
wrote:

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select is coming up
highlighted as an error in the debugger, any clues?



"Luke M" wrote:

Assuming a header row in row 1, you can use these two lines of code to select
only visible data starting in row 2:

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason G" wrote:

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

'Some code to find first blank cell in a column:

'Find row number of first blank cell in column A
x = Range("A:A").Find(What:="", SearchOrder:=xlByRows).Row


For your second question, I am unsure. I can't get that line of code to
produce any error on my hand. Perhaps the fastest solution would be to record
the action and reproduce? Record a macro of you selecting cell A2, and then
hitting Ctrl+Shift+End.
Stop recording, and see what the coding says.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason G" wrote:

thansk, this helped loads, although it prompted another query, while running
different filters to grab different chunks of data if i manually select the
first empty row in my destination worksheets this is giving me the same
problem when recording my macro, i.e. it's recording a macro with a row
number in it, what's the code equivelent for 'first empty row'?

many thanks.....



"Luke M" wrote:

Assuming a header row in row 1, you can use these two lines of code to select
only visible data starting in row 2:

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jason G" wrote:

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

Gord and Luke

many thanks for your help, have finally fudged a working macro, now to test
it against different datasets

many thanks again though guys, coding is not my bag!



"Gord Dibben" wrote:

Have a look at the code Ron de Bruin provides for filtering one sheet to
many sheets.

http://www.rondebruin.nl/copy5.htm


Gord Dibben MS Excel MVP

On Tue, 1 Dec 2009 06:16:02 -0800, Jason G
wrote:

I'm recording a macro in excel, in short i have a s/sheet template with 5
worksheets in it and i want to record a macro to run the following;

I paste a dataset into worksheet A
i autofilter it and cut out chunks of data based on the autofilter
selections and paste it into the other sheets based on several different
filter selections on a couple of different columns, leaving me with a
percentage of the original dataset in worksheet A and chunks of it in
worksheets B-D

now when i autofilter i select all rows under this filter to cut and paste
into one of the other worksheets

in my macro this reads as a long list of the row numbers of the rows in
question from the instance in which i record the macro, but next time i want
to run this macro i want it to just take the rows displayed under the
autofilter selections, not the row numbers that were relevant first time
round.....

how do i get it to pick up on the fact that i'm cutting out the results (all
rows) of a specified autofilter criteria rather than specific rows?

.