Chris Allen
05-27-2007, 12:28 AM
Hi all and sundry
I am consolidating data from various sheets into a summary sheet. My Macro selects certain ranges and posts them into the summary sheet in a certain position. Its works fine except when the individual source sheet has a filter in place. Here it only copies the visable data to the consolidation sheet. I want to leave the filter in place on the source sheet.
Is there a way to copy all data from a range ( including hidden , filted out data) or do I need to collect Filter criteria, turn off, copy data and put filter back with orginal criteria?. Is there an easy way to collect all Filter criteria?....is there a criteria property of the autofilterobject that can be stored and then put back after the copy or do I need to loop through all fields and collect criteria into an array and then use this to put the criteria back in place after copy?
Many thanks in advance
Chris
I am consolidating data from various sheets into a summary sheet. My Macro selects certain ranges and posts them into the summary sheet in a certain position. Its works fine except when the individual source sheet has a filter in place. Here it only copies the visable data to the consolidation sheet. I want to leave the filter in place on the source sheet.
Is there a way to copy all data from a range ( including hidden , filted out data) or do I need to collect Filter criteria, turn off, copy data and put filter back with orginal criteria?. Is there an easy way to collect all Filter criteria?....is there a criteria property of the autofilterobject that can be stored and then put back after the copy or do I need to loop through all fields and collect criteria into an array and then use this to put the criteria back in place after copy?
Many thanks in advance
Chris