Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from large range | Excel Discussion (Misc queries) | |||
Extracting values from a single dynamic cell to a list daily | Excel Worksheet Functions | |||
Extracting the most frequently occuring text from a range | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming |