Need help to filter data and export

[vietdieu]

Hi experts,
I really need help on this thing. I have 1 data sheet (daily updated), the other I want to filter out by Year/Month (and if possible sort in Category Name/Product Name) which definitely need a macro as I don’t want to mess with the data sheet and then I want another macro to export it to a new workbook to send/save by month.

Thank you so much!!

[Mordred]

Hi vietdieu, see attached file. It doesn't export but it sorts. Simply select your criteria (year and month) and then click the button "Monthly Report". It also creates a Total and will prompt you if the selected year or month does not exist in your data. Let me know if this meets your sorting criteria. Also, take note the new formatting in the F and G columns of your data sheet. When new data is added to this sheet you'll have to drag down the formatting of F and G accordingly. Hopefully that doesn't throw off your sheet when you are updating the file. Again, let me know.

[vietdieu]

Thanks a lot. But can we eliminate the F and G col, actually I have more than 100 cols so don't want to have more things here (including the formulas for each col), but anyway if it's a must I can add them in the cols all the way to the right then hide them, also with the years and months (does it effect the macro if it's hidden?).

- About the formatting, I will format the whole col so the macro just can filter whatever in the cells and put it in the sorting sheet. (So "Macro 1" is not necessary.)

- The Total you add in is quite nice, thank you for that. I saw the part of code doing the sum, I will add more sum in this way

- Can you add "All" in the Month Selection so it will filter in a whole year too. I know this will take more of your time, but..please..

Thank again!!!!

[Mordred]

Hi vietdieu, you can hide the columns and the code will still work but if you want to delete them then there will definitely be errors. You can move them but make sure that F and G continue making the same cell references. I should be able to add all of a year but I may need to add a check box or something of the sort to first page. Let me look into it for you.

EDIT:Also, if you do want to actually move the columns to the far right and hide them, the code will have to be altered to accommodate. I will tweak your workbook to do just that and adjust the code accordingly.

[vietdieu]

This is not hurry, Take your time. Thanks!

[vietdieu]

Ii tried to find if there is anyone has the same problem and hope to take off 2 col of Year and Month and I found this, I don't know if it helps:

Please Login or Register  to view this content.

[vietdieu]

Anyone please help me with this!!! Millions thanks!

[vietdieu]

It's been a while now I still need help on this. Thanks for your help!!

[jaslake]

Hi vietdieu
In exporting the worksheet to a new file is the Path and File Name fixed or do you wish the User to select the Path and File Name?

[jaslake]

Hi vietdieu
This code is in the attahed and appears to resolve these issues

hope to take off 2 col of Year and Month

add "All" in the Month Selection so it will filter in a whole year

Let me know about the Export issue and how you wish to deal with it.

Please Login or Register  to view this content.

[jaslake]

Hi vietdieu

I've added this code

Please Login or Register  to view this content.

and attached it to your Button "Export to new Workbook". The code creates a new .xls Workbook in the current Folder (the Folder Path of the Workbook that's running the Code). This can be changed based on your need. Application.DisplayAlerts have been turned off...so, the file will be recreated (overwritten) without warning.

The new file will be named based in the Values in Sheet "ToSort" Range D2 and D3 (for example 2012_All.xls). They're being saved as .xls for compatibility issues but this can be changed. The new File has all Buttons and Validation removed and all Cells have been locked such that the User only sees the Data.

Let me know of issues.