Back for more: Excel macro required... I think!

    I was here a few months back to to refresh this is my setup;
    I have an excel file with 12 worksheets (see attachment) Within the sheet I have a column marked O/S this will feature a Y if it is outstanding and nothing if not.


    I need to get it so that at the click of a button every row that has a Y is pasted into another worksheet, sorted into date order and totalled.


    My problem is this; I have set the worksheets up to have 29 rows, however should the user require more then they can insert new ones, below the dashed line are formulas and nothing must be done beneith this line.


    I can get a macro to autofilter the Y only columns on eacvh sheet but cant get it to copy the area as it differs from month to month.


    I really need help with this and whilst I know my explanation might be a bit hard to understand would be really grateful for anyones help.


    Thanks in advance

    Hi sdj,


    (I only saw the O/S column in July Sheet - assume all sheets will be the same eventually)


    My initial thoughts would be to set up Dynamic Named Ranges for each month worksheet. Example the range for July would be - -


    =OFFSET(JUL!$A$1,0,0,COUNTA(JUL!$A:$A),28)


    This would expand the range as users add rows whenever there is anything in Col A - - you'd just have to keep stuff out of Col A in your formula section below the dotted line.


    Then you could use the Advanced Filter function - set up a criteria in 2 cells somewhere in the workbook - - O/S is cell 1 and "Y" in cell 2. When you run advanced filter from the menus you can copy the results to another location - but it must be on the same sheet as the source data - if you use the same code in a macro, you can modify it to write to a different sheet. Do 1 advanced filter line in the macro for each month.


    This may get you started - - I'll have a try at some code later when I get some free time.


    Hope this helps


    .....Ralph

    Thanks for your post.
    Im afraid im a bit lost with what you have suggested.
    Not your fault at all, its just been one of those days and my brain is fried!


    I think I will go get something to eat, have a beer and come back to read your post.
    Thanks mate, ill let you know how I go.

    I was thinking along similar lines to Ralph, but was wondering if you really needed to have separate tabs for each month. The attached shows an altered data structure that puts all data on the same sheet. The analytic things you were doing are on the Info sheet.


    A Pivot sheet (the one shown is not very elaborate) allows you to pick month (by number -- this could be changed to Alpha) and or O/S descriptor your want to look at.


    The data themselves are sparse and obviously made up, so they may not resemble anything you are used to looking at.


    The Pivot Table and the Info sheet formulas all refer to Dynamic Named Ranges so that users can still add rows/data to the Data sheet and things will update automatically (Pivot table needs to be refreshed to display updates).


    Anyway, another thought for how to approach your data keeping and analysis. I think this structure simplifies the analysis part and gives you a lot more flexibility for changing views.


    NOTE: I wasn't sure what determined what entries went in which of your Month tabs. The Month label I put in column B was based on the made up date in column A. If one of your Date columns correlates to the Month tab, then the column B:D formulas could point to that and column A would not be needed. (Except you might want to copy the other column into column A and then delete the other column, because the Dynamic Name Ranges all refer to column A to count the number of data rows. Deleting column A probably will confuse Excel badly.)


    Anyway, for what it's worth, you can ponder this approach for a bit.

    Thank you for your post.
    Your modified file does seem to be the logical approach. My problem is Im not really in a position to make such alterations.
    The system currently in place contains 3 such files for each advisor, a file which reports on the types of business written for compliance purposes (this goes automatically into a format which is acceptable by the FSA) further files pull in data in different formats and then formulas are in place to calculate salary etc for the advisors and commissions due to introducers.


    The layout of these files was given tome and I was under instruction not to change this only to input formula etc to ease processing (something to do with people not liking change!)


    The other problem is that I work for a firm of accountants and was given this project to do for our sister company (being a Financial services firm) and being accountants my time was billed to them and they have now come back with a new requirement (being the automated list of O/S flagged transactions) even if I had the time to change the system it would not go down well.


    I thank you for your suggestion and apologise if it seems to be of a wasted effort, I only hope you didnt spend to much time on it!


    Thanks again

    Hi sdj,


    I had a bit of time to play around with the code I spoke about. Result is attached.


    Run the macro called "Summarize". I only coded the macro to process January (because it was the first sheet) and July (because it was the only sheet with data). You'll have to define the Dynamic Named Ranges (under Insert / Name / Define) for the other months and add the code to the macro where indicated.


    Wasn't sure which field you want to sort and which to total, so I took a guess. You can mod as needed.


    Each time you run the macro it clears the old data from the Summary worksheet - don't change / delete rows 1 or 2 - I put the Criteria for the Advance Filter way out in IV1:IV2.


    Post back with any questions or if you're looking for a different approach.


    .....Ralph

    Thanks mate, I will take a look at that.


    I had come up with some code myself but im sure yours is far superior.


    I will let you know how I get on


    Cheers

    Hey, Just started to look at that code you did for me a few days back, sorry I havent got back to you sooner its been mad!


    Anyway I think I did all you told me to do but I have a small problem.


    I have entered some dummy data in to Jan and for some reason its not picking it all up in the summary.
    Maybe its just my eyes but I cant see what the problem is.


    I havent tried the other months yet but will be getting the real sheets which have data from July through to October so I will try it on that also.


    I have attached the modded file if you want to see what I have done


    Cheers
    Steve

    Sorry, I know its been more than a week but its been a hell of one I can promise you.


    Anyway, following my last post that didnt have the file attached, Im having another go.


    I think I have done everything you told me but for some reason not all of the data is being picked up in the summary sheet. I have attached the file (v2) and you can see what I mean.


    Would be most grateful for your help.


    Cheers

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login