+ Reply to Thread
Results 1 to 18 of 18

macro to filter data table and copy it to another worksheet

  1. #1
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    macro to filter data table and copy it to another worksheet

    Dear experts.
    I do not think formula will help in this case and as I am not a VBA expert I will appreciate any help.
    I need macro which will filter data table with conditions chosen from drop down lists and then copy resulted table into the second worksheet under the drop down lists.
    Unfortunately I am an absolutely VBA beginner so can't do it on my own.
    Is this even possible - please help.
    Please note: I know I can apply filter without macro but I don't want to do this in that case because of character of report I would like to make.

    I hope there will be somebody who will help me with that...
    Many thanks.

    PS. file attached.
    Attached Files Attached Files
    Last edited by jarssonn; 04-06-2010 at 03:21 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    This will create the report part and print if you wish. You'll need to demonstrate what you're after with a chart based on a sample result.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: macro to filter data table and copy it to another worksheet

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

  4. #4
    Forum Contributor
    Join Date
    06-27-2009
    Location
    montana,USA
    MS-Off Ver
    Excel 2007
    Posts
    190

    Re: macro to filter data table and copy it to another worksheet

    Wow! That is really cool.
    One question though. After you generate a report, how do you delete the report to create another? Just change the criteria?
    Last edited by simpson; 04-03-2010 at 11:19 PM.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    Sounds like you didn't try it...yes, change the criteria and run it again.

  6. #6
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    Re: macro to filter data table and copy it to another worksheet

    Quote Originally Posted by JBeaucaire View Post
    This will create the report part and print if you wish. You'll need to demonstrate what you're after with a chart based on a sample result.
    Many thanks for that!!!
    Is there possibility to add totals, averages etc. to generated table (e.g. average efficiency, total units produced etc.)?
    Also as you probably discovered my cats for drop down lists contains a lot of blank cells in case some more lines, operators etc will be added, is there an option not to show those blank cells in drop down lists if you see what I mean?
    About graphs - I am looking for graphs of results so for example efficiency at plan std (see attached file - I have made simple graph based on generated table), what I want is to get graph like this one for all info in table (also efficiency@100%, labour utilisation, total downtime, units produced) and then for efficiency graphs (both @plan std and @100%
    std) put base at 75% and all results below to be shown in red and all results at 75% and over to be shown in green.
    Ufff I know it's a lot and you've already helped me much but I just want to make this as perfect as possible... (but unfortunately I am not good in VBA as I wanted to be)

    Oops, and just one thing - you will see that in file I've attached, I have selected dates (from 1/1/10 to 3/1/10) and table shows also 4th and 5th...
    Also it is not generating report if I choose start date different than 1/01/10...

    Many thanks for all your effort to help me.
    Attached Files Attached Files
    Last edited by jarssonn; 04-04-2010 at 02:34 AM.

  7. #7
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    Re: macro to filter data table and copy it to another worksheet

    Quote Originally Posted by Paul View Post
    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!
    Sorry for that, I just was really desperate to get help. This will be good lesson for me because I haven't read forum rules good enough. I can assure you this will not happen again.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    1) The named ranges on the sheet CATS are now dynamic. There is a different Name Range formula for the text lists vs the numeric lists (the dates are numeric).

    2) All the dates work for me, can't speak to why it doesn't work for you.

    3) Please mockup a sample of the totals you want added based on the data currently showing on the report. Adding formulas to the bottom of a report is easy, I just need to exactly see what you expect the answers to be. I can automate it.

    4) I don't do a lot with charts. You will have to turn on the macro recorder and let it record you setting up the chart to display exactly how you want based on the data currently showing on the report. After you've got the chart looking just right, turn off the recorder and post the recorded code here and I'll see if I can tweak THAT into a permanent macro for you.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    Re: macro to filter data table and copy it to another worksheet

    Quote Originally Posted by JBeaucaire View Post
    1) The named ranges on the sheet CATS are now dynamic. There is a different Name Range formula for the text lists vs the numeric lists (the dates are numeric).

    2) All the dates work for me, can't speak to why it doesn't work for you.

    3) Please mockup a sample of the totals you want added based on the data currently showing on the report. Adding formulas to the bottom of a report is easy, I just need to exactly see what you expect the answers to be. I can automate it.

    4) I don't do a lot with charts. You will have to turn on the macro recorder and let it record you setting up the chart to display exactly how you want based on the data currently showing on the report. After you've got the chart looking just right, turn off the recorder and post the recorded code here and I'll see if I can tweak THAT into a permanent macro for you.

    Thank you for changes you've made to Named Ranges.
    I can't understand why those dates don't work for me, I've tried few times on different computers and different excel versions (2007,2003) and still when I choose any start date other to 1/1/10 plus any finish date it says "none found". And when I choose 1/1/10 till for example 3/1/10 it shows all table (till 5/1/10 as I had in example file).
    It also did not work when I want to change date ranges (I did want to set up 28/2/10 as the first date available as my reports will contain data collected from that date)
    Very strange...

    I have attached file with totals added to example table so maybe you will be able to automate this by macro.
    And I have recorded two macros for creating graphs, hope this also will be possible to automate.
    Anyway thanks for all your help till now and I hope to hear from you soon.
    Attached Files Attached Files

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    Phew...this was a bit of work.

    I've added a "charts" option so you can easily turn them off/on for some reports, some will have such little data that a chart is probably pointless.

    The dates available to "choose" are from your lists on the CATS sheet, so any dates you want to appear in the drop downs must be in those lists somehow, too.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    Re: macro to filter data table and copy it to another worksheet

    Thank you very much - you are amazing!
    Report is 100% as I wanted it to be - the only thing I can't understand is why I got this problems with choosing range...
    File you have uploaded had date range selected as 2/01/10o 2/01/10 I've seen the result table and graphs but when I press 'Generate report' with same dates remaining it's straight away changing to 'none found'. Have you got any idea why this is happening?
    I have uploaded file saved when dates are selected as 2/01/10 to 2/10/10 and result is 'none found - could you please download it one more time and check whether you got table and graphs generated or you see that 'none found' message the same as me.

    Anyway many thanks for all your effort.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    I'd guess it has something to do with the fact that the dates are displaying in one format on the CATS page and in another format on the report. Maybe if you sync up the cell formatting on all sheets.

    Uploaded a file where?

  13. #13
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    macro to filter data table and copy it to another worksheet

    Sorry I do not understand why file haven't been uploaded. It is uploaded now.
    I do not know if I understand correctly what do you mean by 'Maybe if you sync up the cell formatting on all sheets'. Did you mean to set the same (number) date format in format cells? If yes I've tried this as well and this is not changing anything...
    Still does not work as I explained yesterday.
    Are you also having the same problems when you open that file?
    I am really sorry that I have to bother you every day but I am really desperate to have this file working.
    Many thanks
    Attached Files Attached Files

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    When I open the file above the screen appears with nothing, the way you left it.

    When I immediately click GENERATE REPORT, the data and charts appear. I am having no problem.

    Try not using the button. Go into the editor and use the F8 key to start the macro after you click inside the routine. This will cause Excel to step through the routine 1 line at a time, each time you press F8 it will progress one line. This will allow you to switch back and forth between the sheet and the module to see what is going on.

    I suspect you're having some unseen issue with the AUTOFILTER section. Each line of code in the Autofilter section is reducing the number of rows showing on the "Data" sheet. When it gets through it, it does a LastRow test to make sure there is at least one row of data showing. If the user-inout cells D9, D7 or D11 are empty, they should not be used to filter the data.

    Perhaps something on your system is allowing:
    1) the empty cells to still trigger a filter on their respective columns
    2) the data fields D4 and D5 are filtering out all the rows because the data is being read oddly on your own system

    F8 your way through the code and keep looking at the sheet, spot where the wrong thing happens.

  15. #15
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    Re: macro to filter data table and copy it to another worksheet

    I've tried already that step by step 'running' macro and for some reason it's not filtering correctly.
    I do not quite understand what did you mean by "...Perhaps something on your system is allowing:
    1) the empty cells to still trigger a filter on their respective columns
    2) the data fields D4 and D5 are filtering out all the rows because the data is being read oddly on your own system"
    .
    But I've sorted this problem different way - I've added extra column in data worksheet which contain the same date as next column but formated as number as well as I did the same with start and end dates on my report worksheet. I can still choose dates as normal because I put formula to transfer that date to next column and over there is formated as number (and is unseen due to white font). Then I just adjusted CreateTable macro to filter column with numbered format of date by conditions formated the same way and it works perfectly!!!
    Is there an option to add some code to the macro that dates will be readable in number format?
    If not then no problem, report is working anyway.
    Thanks for all your help - you are my master!

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: macro to filter data table and copy it to another worksheet

    The macro reads the dates in number format. As I mentioned, there is something going on with you that I do not observe, the dates as you offered in post #13 that didn't work for you worked immediately for me.

    I wish I knew why you were having issues. If I was looking over your shoulder I'm sure we'd figure it out, but this the best we have.

    I'm glad it all works for you now like intended.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  17. #17
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: macro to filter data table and copy it to another worksheet

    hi all,

    I realise this thread is now Solved but...

    The date issue when autofiltering may relate to differences in international settings esp. with JB in the States & Jarsonn in the UK. The below links offer some explanation of how autofilter treats dates when filtering:
    http://www.ozgrid.com/VBA/autofilter-vba-dates.htm
    http://www.thecodecage.com/forumz/ex...utofilter.html

    Note Chip's warning/advice in http://www.databaseforum.info/16/5/2...154edb957.html

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  18. #18
    Registered User
    Join Date
    04-01-2010
    Location
    Worcester, UK
    MS-Off Ver
    Microsoft 365 (subscription)
    Posts
    50

    Re: macro to filter data table and copy it to another worksheet

    hello, thank you very much, that's very interesting, I will look at it a.s.a.p.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1