• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter based on cell value and then export results to new sheet

The intent of my spreadsheet is to use it as a 1 stop shop for pricing and tracking project variations. It is structured as follows;
1. Register - Summarises all data from Variations sheet
2. Variations - Data entry sheet to populate all variations information
3. Database - List of materials and their respective rates
4. Submission - Final submission sheet to submit to client
When I create a new variation, I register the information on Sheet1. I then enter data on Sheet2. I would like to able to filter Sheet2 to only show records where VO No (Column E) matches value in Cell AB1 (currently filtered on sample). I have added a macro button (right of Sheet2, Cell AB1) for this but am looking for the code to do this. I have added another button below to unfilter and show all VO No's. Lastly, I have added a 3rd button to export filtered results onto Sheet4 (or a new sheet with a save as prompt). I need this export to maintain all formatting so exported data looks same as filtered data on Variations sheet. Refer attached file.
Thanks in advance.


Hi Nebu.
Works great. Is there any way to export all formats as well? (numbers, alignment, borders and shading) I have split the description field on the Variations sheet because I need to add some data above the headers and need to align it differently. Due to this I have had to shade the cells below these headers in white so as to hide the gridlines.
Thanks again Nebu.
Still not quite there though. I've looked at your code and can see PasteValuesAndNumberFormats and then you have applied various other formats. Is it possible to paste everything except for formulas? This would then include all formats that I'm wanting ie numbers, alignment, borders and shading.


Excel Ninja

It is not possible to copy in any other format because of the formulas you have on your variation tab. I have edited the Submission tab in Post #4 to look exactly like what you had given as an example in your original file. Download the file I have uploaded in the post #4 once again and check. If you need any other customized formatting you will have to code it.