Extracting a dynamic range
 

I have a table that looks like this:
B C D E F G H I J K L M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.

Extracting a dynamic range
 

Try this.

Turn on the macro recorder.
Do an Autofilter on the table.
Select the due date you want.
Select Visible Cells -- This is the trick.
Copy.
now paste where you want.

Select Visible cells is found under Edit, Goto, Special Cells,

There is also a toolbar button you can put on a custom toolbar the is very
useful.
Right click a toolbar, Customize, Commands Tab, Edit, next to the last entry
on the right list.

See if that gets you close. I think it will be simplier than an Advanced
Filter or some several step OFFSET range.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Paul" wrote in message
...
I have a table that looks like this:
B C D E F G H I J K L M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.

Extracting a dynamic range
 

If copying a filtered range, you don't need the "trick". The default
behavior is to copy only the visible rows so you don't have to resort to
specialcells. This has been true at least since xl97. This is true whether
done manually or in code.

--
Regards,
Tom Ogilvy


"Stephen Rasey" wrote in message
...
Try this.

Turn on the macro recorder.
Do an Autofilter on the table.
Select the due date you want.
Select Visible Cells -- This is the trick.
Copy.
now paste where you want.

Select Visible cells is found under Edit, Goto, Special Cells,

There is also a toolbar button you can put on a custom toolbar the is very
useful.
Right click a toolbar, Customize, Commands Tab, Edit, next to the last
entry
on the right list.

See if that gets you close. I think it will be simplier than an Advanced
Filter or some several step OFFSET range.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Paul" wrote in message
...
I have a table that looks like this:
B C D E F G H I J K L M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.

Extracting a dynamic range
 

Steve,
It won't work, I will have to input the date myself in the
filter. And then remove the filter before the macro ends.
What I was thinking is for the macro to pick the date from
a preset cell where I would store the wanted date.

Thanks anyway....

-----Original Message-----
Try this.

Turn on the macro recorder.
Do an Autofilter on the table.
Select the due date you want.
Select Visible Cells -- This is the trick.
Copy.
now paste where you want.

Select Visible cells is found under Edit, Goto, Special
Cells,

There is also a toolbar button you can put on a custom
toolbar the is very
useful.
Right click a toolbar, Customize, Commands Tab, Edit,
next to the last entry
on the right list.

See if that gets you close. I think it will be simplier
than an Advanced
Filter or some several step OFFSET range.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Paul" wrote in
message
...
I have a table that looks like this:
B C D E F G H I J K L
M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist
Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.


.

Extracting a dynamic range
 

Why do you think you can't do it all with code?

--
Regards,
Tom Ogilvy


"Paul" wrote in message
...
Steve,
It won't work, I will have to input the date myself in the
filter. And then remove the filter before the macro ends.
What I was thinking is for the macro to pick the date from
a preset cell where I would store the wanted date.

Thanks anyway....

-----Original Message-----
Try this.

Turn on the macro recorder.
Do an Autofilter on the table.
Select the due date you want.
Select Visible Cells -- This is the trick.
Copy.
now paste where you want.

Select Visible cells is found under Edit, Goto, Special
Cells,

There is also a toolbar button you can put on a custom
toolbar the is very
useful.
Right click a toolbar, Customize, Commands Tab, Edit,
next to the last entry
on the right list.

See if that gets you close. I think it will be simplier
than an Advanced
Filter or some several step OFFSET range.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Paul" wrote in
message
...
I have a table that looks like this:
B C D E F G H I J K L
M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist
Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.


.

Extracting a dynamic range
 

I learn something new every day. This wasn't the only thing today.
Autofilter automatically copy visible cells only.

However If rows are Hidden, then you must use select visible cells to not
copy the Hidden cells.

The difference in behavior is dependent upon whether Autofilter has a
condition applied.
Hiding rows after filtering, then copying the block, the manually Hidden
rows are not copied.
Having Autofilter On, but showing all rows, then manually hiding some rows,
the hidden rows WILL be copied.

Grouping rows, colapsing them. Copy will copy the collapsed cells.
Apply an AutoFilter, then Group some rows, Copy will not copy the
collapsed cells.

Thanks for the correction.
Stephen Rasey
Houston

"Tom Ogilvy" wrote in message
...
If copying a filtered range, you don't need the "trick". The default
behavior is to copy only the visible rows so you don't have to resort to
specialcells. This has been true at least since xl97. This is true
whether
done manually or in code.

--
Regards,
Tom Ogilvy


"Stephen Rasey" wrote in message
...
Try this.

Turn on the macro recorder.
Do an Autofilter on the table.
Select the due date you want.
Select Visible Cells -- This is the trick.
Copy.
now paste where you want.

Select Visible cells is found under Edit, Goto, Special Cells,

There is also a toolbar button you can put on a custom toolbar the is
very
useful.
Right click a toolbar, Customize, Commands Tab, Edit, next to the last
entry
on the right list.

See if that gets you close. I think it will be simplier than an
Advanced
Filter or some several step OFFSET range.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Paul" wrote in message
...
I have a table that looks like this:
B C D E F G H I J K L M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.

Extracting a dynamic range
 

Maybe I should have said, when applying only the autofilter and copying the
filtered data, the rows left visible by the autofilter are copied by
default. <g

Thanks for the information on the other combinations and permutations.

--
Regards,
Tom Ogilvy


"Stephen Rasey" wrote in message
...
I learn something new every day. This wasn't the only thing today.
Autofilter automatically copy visible cells only.

However If rows are Hidden, then you must use select visible cells to not
copy the Hidden cells.

The difference in behavior is dependent upon whether Autofilter has a
condition applied.
Hiding rows after filtering, then copying the block, the manually Hidden
rows are not copied.
Having Autofilter On, but showing all rows, then manually hiding some
rows,
the hidden rows WILL be copied.

Grouping rows, colapsing them. Copy will copy the collapsed cells.
Apply an AutoFilter, then Group some rows, Copy will not copy the
collapsed cells.

Thanks for the correction.
Stephen Rasey
Houston

"Tom Ogilvy" wrote in message
...
If copying a filtered range, you don't need the "trick". The default
behavior is to copy only the visible rows so you don't have to resort to
specialcells. This has been true at least since xl97. This is true
whether
done manually or in code.

--
Regards,
Tom Ogilvy


"Stephen Rasey" wrote in message
...
Try this.

Turn on the macro recorder.
Do an Autofilter on the table.
Select the due date you want.
Select Visible Cells -- This is the trick.
Copy.
now paste where you want.

Select Visible cells is found under Edit, Goto, Special Cells,

There is also a toolbar button you can put on a custom toolbar the is
very
useful.
Right click a toolbar, Customize, Commands Tab, Edit, next to the last
entry
on the right list.

See if that gets you close. I think it will be simplier than an
Advanced
Filter or some several step OFFSET range.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Paul" wrote in message
...
I have a table that looks like this:
B C D E F G H I J K L M
Vch V# Name Inv Date Due Amt Act Dpt Description Dist Batch

"Vch" is in cell B4 and the data starts on row 5.

The sheet is about 3000 rows tall and will grow to to
about 10000 by year end.
I need to extract (copy and paste/append to another
worksheet) a dynamic range that will change each week.
That range will be determined by the values in column G
(Due Date).
For example, this week, I have manually extracted the
following range (B1200:M2350 : all payments with a due
date equal to "08/23/04"

I need help with a macro that could do this, I think I
will be able to write the "append" part of the macro.