+ Reply to Thread
Results 1 to 20 of 20

Need a macro to create a summary sheet

  1. #1
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Need a macro to create a summary sheet

    I have been searching all morning for a topic similar to this but none are quite right for my situation.

    I need to take only selected sheets (daily sheets names Monday through Friday) in a workbook and compile them into a summary sheet for the week named "Summary". My issue is that the daily sheets will have lines added by users that could exceed 50+ and these changes need to be automatically updated into the summary without having to manually running the macro again.

    I also need a macro to take the totals line in each day to copy into a separate sheet (Totals) with only the daily totals to be displayed. This also needs to automatically update when anything is added.


    Attached is a working copy of the file.

    Edit - cross posted to http://www.mrexcel.com/forum/excel-q...ml#post3706782
    Attached Files Attached Files
    Last edited by rgeorge; 02-07-2014 at 12:27 PM.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Need a macro to create a summary sheet

    The only way I know to do this is to have a Event procedure to keep Summary and Totals sheets up to date.
    And it also depends on the summary layout which is empty in your workbook. WHat information do you want to summarize?

    Another way to get similar report with a lot more flexibility would be to have all those informations entered into ONE sheet which would have a new column named DAY and to use Pivot Table.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    I wanted to be able to take the info from the pages titled Monday, Tuesday, Wednesday, Thursday, and Friday and have those all consolidated in a Summary page that is updated as soon as new info is added in one of the individual days sheets. I want to have the individual sheets in one so that its easier to scroll through to find where a entry is instead of jumping through each sheet. The layout should be exactly the same as each sheet - just pull a copy over to the summary. The main issues I have is that rows will be added as needed to the daily sheets and this needs to be incorporated into the summary and I only want the daily sheets - not the others to be summarized. I looked into a pivot table but I am trying to make it a little easier to use for the end users and we haven't had much luck with the pivot tables before. I was able to get a fairly simple macro that was able to pull the info but it was taking every sheet - not just the ones I wanted. It also would not update when I made changes to the fields in the daily pages.

    I was able to figure out the totals to work with just a formula and adding lines is not messing it up so that part is solved.

  4. #4
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    This post was close to what I am looking for but it takes every sheet from the workbook. How do I adapt this to only grab the sheets I want to be summarized?

    http://www.mrexcel.com/forum/excel-q...ary-sheet.html

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Need a macro to create a summary sheet

    I would not do that if I was you. It would take lots of time every time you change a cell in a sheet. It would also mean the macro would have to erase the Summary sheet and rebuilt it every time you change a cell in one of the day sheets.
    You're looking for trouble here.
    I really suggest you to try the pivot table and get the users used to it. It works just fine, it's fast and very flexible.
    See the attached workbook about how I see this. The Summary sheet (which should be reanmed Data) has a dynamic named range (Data_Source) so that the pivot table has always all your data in it.
    It gives you your sheet Totals as a bonus. One less sheet in your workbook.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    That looks very nice. I will see if the users are willing to give it a try. They can be very resistant to change. The current system is a dry erase board so this is a huge jump forward for them.

    Thank you very much for the help!
    Last edited by rgeorge; 02-06-2014 at 01:01 AM.

  7. #7
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    I tried to make it a pivot table but there were too many issues with it since the file needs to be shared on a network location for multiple users to update. The pivot table will not update while its a part of the spreadsheet and had to be pulled out into a separate file. Both the bosses and users didn't like it so I have to try a new method.

    I'm back to a macro that will copy an paste the daily sheets onto a summary sheet whenever info is changed. It will have to delete any old info on the page so it doesn't just duplicate the info below the last set that was pasted. It also needs to take only selected sheets in the workbook. If anyone else has suggestions on another way that I could go, I'm all for trying out something else too.

    Thanks in advance.

  8. #8
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    Hi rgeorge,

    just a quick question. If the data on each page is "the same" AND the daily sheets, Summary and totals are in one workbook, how about having a "template" tab that the user completes and then have a macro behind that would add the data tot eh right daily sheet and update the other pages behind the scenes.

    This means standard inputs (could even be a user form if you wanted although a little more time to develop solution) for everybody, one place to edit inputs for any validation checks like mandatory fields etc and not having to update everything every time a cell changes in the workbook (a real performance hit as @Pierre points out).

    I have possible code that would take little changes to get this form of solution up and running pretty quickly

    Jmac1947

  9. #9
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    I'm open to any way to get this put together that's user friendly and allows management to be able to get a summary of the totals for the week. The users will need to be able to easily find entries when a customer asks about it and warehouse personnel can get reports printed to ensure that the office and warehouse are on the same page. That's the reason I was thinking about a weekly page that's easy to scroll through and daily pages that can be printed for the warehouse. I never even thought about a template... that could be a perfect solution.

  10. #10
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    leave it with me, I will post one possible solution in a couple of hours, need to talk to wife and have dinner first

    jmac1947

  11. #11
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    rgeorge-jmac.xlsm

    Hi,

    take a look at the attached spreadsheet.

    It is still a work in progress but I hope you can see where I am going, what the process is and take some value from the code.

    I have seem that this is your 6th post on the forum so I have tried to take a sloow and methodical approach to the code, I know that others on this forum can produce much better (that is let VBA do some of the heavy lifting) code but I figure, slow and steady may be a better introduction.

    let me know if you have any comments.

    Note that I went via the template option, I will add an image and tie it to the macro in my next version, just wanted to check in and see if I m heading in right direction

    Cheers

    Jmac1947

    1. Please consider clicking on the * Add Reputation if you think this post has helped you
    2. Mark your thread as SOLVED when question is resolved

  12. #12
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    I never thought about this way to go but it looks like it would work very well. It gives them one place to enter info and it populates the fields in each sheet.

    The info you have in the template is all they need to add so that is perfect. I looked at the code and I see where you're going with it. I have taken a college class in Excel but they didn't get into macros at all so I am new to it. That seems very straightforward and it seems to do everything I was looking for.

    My only question is will deleting info at the end of the week cause any damage to the process? They clear out the list for a fresh start every week. Normally they would just delete the rows they filled in so I don't want that to mess up any coding that you worked on.

    Thank you for all your help.

  13. #13
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    ahhhhhh, beware the late information..

    so what gets cleared, by whom and when please

  14. #14
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    lol... sorry about that.

    The prior weeks entries are cleared by one person at the start of the new week (monday morning). Before it was just a dry erase board but the quantity is starting to exceed that method and a office re-design is making it impossible to keep. There's no need to keep the info after the shipments have gone out so it gets erased and they start the new week fresh. That's why I had a sheet for next weeks shipments and a pending sheet for the orders that are received but not scheduled for shipping yet.

    Management sees the totals but not until the shipment has actually gone out so they want to see whats planned to track productivity of the office and be proactive if goals are not being met.

  15. #15
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    My apologies. I was rushed last night when I posted the "work in progress" copy and forgot to tell you that i had not run any tests on it.

    I see this morning that I have a couple of logic issues to worry about (wrong information in wrong variable) so I will waitr until I hear back from you on the clearing at week end question before I post a new version for you to check further.

    One more question in the "clearing" area. Is the finished workbook designed to be for one week at a time only? (that is transient working info during the week) or are you planning to keep as a more permanent record?

  16. #16
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    Yes, it's designed for only one week at a time with a section for next week that may be scheduled early and a section for orders not scheduled to be shipped yet. The next week and pending shipping is very small and doesn't need to be anything more than what I already have. Just a simple way to keep track of them.

  17. #17
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    rgeorge-jmac(2).xlsm

    Hi rgeorge.

    I have cleaned up most of the issues i found in testing, sorry it has taken a bit longer than I expected but that 4 letter word (work) got in the way of having fun

    The template page probably needs some "prettying up" before you let it loose, as long as you dont change cell location in Col B no macro change needed.

    I added in an Admin functions page as well. at the moment it only has one entry which is the weekly "delete the weeks data" task.

    have a play and see how you go

  18. #18
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    It works great and the automation is excellent.
    The only things I would like to see changed (and trust me, I tried to do it but failed) is:

    The order weekday field from the template also transfer to the weekly summary page to better identify individual shipments. When I added it, I started having issues with the sorting of the page. I have no idea where I went wrong but it started pulling the header into the data when I sorted. The days were at least moving over like it should.

    A total line on the Summary sheet. I added it and tried to adjust the code to add the next entry above it but it didn't want to work. In that line I was using a formula like =SUBTOTAL(9,C3:C8) so that it would give me a total of the filtered and unfiltered entries. This way we can filter by whatever field they want and get a total for the filtered results. The formula was working but my issue was the new lines being added and the formula expanding to keep all the new entries.

    This experience is really pushing me to learn more about this side of excel and be able to do it myself eventually. All your work is more than just appreciated and if we weren't on separate continents, I would have to buy you a beer or two.

  19. #19
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Need a macro to create a summary sheet

    Gosh, what a great offer, unfortunately I really don't see myself visiting CA any time soon, got as far as Hawaii in Nov/Dec last year, so I guess I will need to source my own "sustance for a hot day" (Melbourne expecting 40+ C tomorrow, about 105 F)

    Based on your last response I think you can probably mark this thread as "solved" and we can continue to "make tweaks" like your last post offline and not clutter up the forum more than needed (as you will have seen, email in comments in code)

    Jmac

  20. #20
    Registered User
    Join Date
    02-03-2014
    Location
    Upland, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need a macro to create a summary sheet

    I agree... marking it as solved now.

    Same issue here - not making it out your way anytime soon. I have visited but the north said up in Darwin for a few days. It was pretty warm up there and it was November. We see temps like that during the summer frequently so I know how you're feeling.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Simple Macro to Create Summary Sheet
    By CRW in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-16-2014, 03:14 PM
  2. Create a summary from multiple sheets on a master/summary sheet
    By detribus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2012, 08:04 PM
  3. [SOLVED] Need a Simple Macro to Create Summary Sheet
    By CRW in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2012, 06:37 PM
  4. Create a summary sheet
    By excel-with-excel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-26-2010, 12:27 PM
  5. Create Summary Sheet
    By AliH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2005, 04:05 AM

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