+ Reply to Thread
Results 1 to 6 of 6

Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet

  1. #1
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2013
    Posts
    142

    Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet

    Hello Everyone,

    I've searched the forum for a solution but haven't been able to find anything that quite meets my needs. I have an Excel 2010 workbook (copy attached) that will be used to track metrics for people in my group over the course of the year. So far I have designed it so each person has their own tab where they can type in their metrics at the end of each week. I'm using a formula to automatically compile the info from each person's tab into the overall worksheet for that week. I then have a macro button that I push that inserts a new worksheet for the coming week, copy's the formulas, does a copy/paste values on the current week, then clears the contents on the input tabs. So I have a nice weekly report with a running YTD column.

    What I'm needing is a macro that can take the information from the weekly worksheets and compile it into a Summary worksheet broken down by month. So, it will somehow need to identify all the weekly worksheets for January and total the cells into the respective location on the summary sheet, then do the same for Feb, March, etc. I know that if I just had 52 weekly worksheets already created I could use a formula to do this. But, I'm assuming I'll need a macro to do it for a dynamicly changing workbook.

    I don't really undersand VBA. What I have in this workbook so far is just stuff I picked up searching the forums. Any help would be greatly appreciated.

    Weekly Report With Summary Template.xlsm

  2. #2
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2013
    Posts
    142

    Re: Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary shee

    **Bump**

    Let me give a little more insight into how I "think" this should work. Basically, I think a macro that had a formula that could search all sheets in the workbook and look at cell C1. For January it would look at C1 in all the sheets and any sheets that had a value between 1/1/13 - 1/31/13 it would add the values in each respective cell in column B and display the total under the "January" column in the "Summary" sheet. Does that make sense?

    Thanks in advance for any help/advice.

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary shee

    Your suggestion might work. So we do not have to deal with the named sheets like Kemo, Linda, etc right?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2013
    Posts
    142

    Re: Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary shee

    That is correct. The sheets with people's names are where they will enter their respective information each week. That information then gets compiled on the sheet named for that week. Then, the next step is where I'm needing the help. I'm looking to have the weekly sheets compiled on the summary sheet by month.

    Thank you

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary shee

    So considering that you run the macro every month, it should sum up the values only for that month?

  6. #6
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2013
    Posts
    142

    Re: Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary shee

    Actually, I run the macro each week. Anyway, I appreciate your time on this, but I decided to redesigned my spreadsheet and have been able to get it to pretty much work the way I need it to.

    Thanks

+ 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