Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Daily, Weekly and Monthly Reports.
In out excel file we have 11 workbooks with running totals that update daily,
weekly and monthly reports in 3 other workbooks. We would like to make up Macros so that the reports will update automatically when run. To do this we need the daily cell reference to move one down during the week, 4 down at week end, and 2 at month end. I think it will take 3 Macros to do this but I have tried to make the reference move down one but it doesn't seem to work. How can I set up Macros to do what I need? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Daily, Weekly and Monthly Reports.
Hi,
Perhaps you can you post samples of the data to go with the explanation. I really don't understand what you are trying to achieve. However, naming cells/ranges is a good start in retrieving the last data copied and then using offset to move down and re-naming to the same name ready for the next time. You can record macros when naming cells/ranges to get the syntax. To name cells/ranges: Versions before xl2007: Menu item Insert - Name - Define xl2007 version: Formulas tab on the ribbon - Define Name -- Regards, OssieMac "Dragon" wrote: In out excel file we have 11 workbooks with running totals that update daily, weekly and monthly reports in 3 other workbooks. We would like to make up Macros so that the reports will update automatically when run. To do this we need the daily cell reference to move one down during the week, 4 down at week end, and 2 at month end. I think it will take 3 Macros to do this but I have tried to make the reference move down one but it doesn't seem to work. How can I set up Macros to do what I need? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Daily, Weekly and Monthly Reports.
I did up the following sample. Does this help?
Sheet1: Date: lbs cases trl: YTD lbs YTD cases YTD trl: 11/26/2007 89450 5640 5 89450 5640 5 11/27/2007 101467 7080 6 190917 12720 11 11/28/2007 45950 1245 2 236867 13965 13 11/29/2007 192658 8023 7 429525 21988 20 11/30/2007 78500 4167 4 508025 26155 24 MONTHLY 508025 26155 24 12/1/2007 102519 7591 6 610544 33746 30 12/2/2007 187662 6482 4 798206 40228 34 Weekly 798206 40228 34 Sheet2: Date: lbs cases trl: cans: rail: YTD lbs YTD cases YTD trl: YTD cans: YTD rail: 11/26/2007 89450 5640 5 0 2 89450 5640 5 0 2 11/27/2007 101467 7080 6 0 2 190917 12720 11 0 4 11/28/2007 45950 1245 2 1 3 236867 13965 13 1 7 11/29/2007 192658 8023 7 1 2 429525 21988 20 2 9 11/30/2007 78500 4167 4 0 3 508025 26155 24 2 12 MONTHLY 508025 26155 24 2 12 12/1/2007 102519 7591 6 2 1 610544 33746 30 4 13 12/2/2007 187662 6482 4 0 2 798206 40228 34 4 15 Weekly 798206 40228 34 4 15 Sheet3: LBS. CS. # TRL LBS. CS. # TRL 11/26/07 518,127 22,479 12 518,127 22,479 12 11/27/07 545,942 26,530 11 1,064,069 49,009 23 11/28/07 302,321 13,633 7 1,366,390 62,642 30 11/29/07 484,423 21,767 13 1,850,813 84,409 43 11/30/07 571,149 24,720 13 2,421,962 109,129 56 MONTHLY 2,421,962 109,129 56 12/01/07 240,656 9,679 8 2,662,618 118,808 64 12/02/07 470,440 20,735 10 3,133,058 139,543 74 WEEKLY 3,133,058 139,543 74 Daily: DAILY REPORT Date: December 2, 2007 LBS. CASES Y.T.D. LBS. Y.T.D. CASES Sheet 1 Sheet1!B9 Sheet1!C9 Sheet1!F9 Sheet1!G9 Sheet 2 Sheet2!B9 Sheet2!C9 Sheet2!H9 Sheet2!I9 Sheet3 Sheet3!B9 Sheet3!C9 Sheet3!E9 Sheet3!F9 TOTAL SUM(E3:E7) SUM(F3:F7) SUM(G3:G7) SUM(H3:H7) # OF RAIL TODAY - Sheet2!F9 # OF RAIL Y.T.D. - Sheet2!L9 DAILY Y.T.D. # OF TRUCKS Sheet1 Sheet1!D9 Sheet1!H9 # OF TRUCKS Sheet2 Sheet2!D9 Sheet2!J9 # OF cans Sheet2 Sheet2!E9 Sheet2!K9 # OF TRUCKS Sheet3 Sheet3!D9 Sheet3!G9 Weekly: Weekly REPORT Week ending: December 2, 2007 LBS. CASES Y.T.D. LBS. Y.T.D. CASES Sheet 1 Sheet1!B10 Sheet1!C10 Daily!G3 Daily!H3 Sheet 2 Sheet2!B10 Sheet2!C10 Daily!G4 Daily!H4 Sheet3 Sheet3!B10 Sheet3!C10 Daily!G6 Daily!H6 TOTAL SUM(E3:E7) SUM(F3:F7) SUM(G3:G7) SUM(H3:H7) # OF RAIL Week - Sheet2!F10 # OF RAIL Y.T.D. - Daily!F10 DAILY Y.T.D. # OF TRUCKS Sheet1 Sheet1!D9 Daily!H11 # OF TRUCKS Sheet2 Sheet2!D9 Daily!H12 # OF cans Sheet2 Sheet2!E9 Daily!H13 # OF TRUCKS Sheet3 Sheet3!D9 Daily!H15 Monthly: Monthly REPORT Date: November, 2007 LBS. CASES Y.T.D. LBS. Y.T.D. CASES Sheet 1 Sheet1!B7 Sheet1!C7 Daily!G3 Daily!H3 Sheet 2 Sheet2!B7 Sheet2!C7 Daily!G4 Daily!H4 Sheet3 Sheet3!B7 Sheet3!C7 Daily!G6 Daily!H6 TOTAL SUM(E3:E7) SUM(F3:F7) SUM(G3:G7) SUM(H3:H7) # OF RAIL Week - Sheet2!F7 # OF RAIL Y.T.D. - Daily!F10 DAILY Y.T.D. # OF TRUCKS Sheet1 Sheet1!D7 Daily!H11 # OF TRUCKS Sheet2 Sheet2!D7 Daily!H12 # OF cans Sheet2 Sheet2!E9 Daily!H13 # OF TRUCKS Sheet3 Sheet3!D7 Daily!H15 "OssieMac" wrote: Perhaps you can you post samples of the data to go with the explanation. I really don't understand what you are trying to achieve. However, naming cells/ranges is a good start in retrieving the last data copied and then using offset to move down and re-naming to the same name ready for the next time. You can record macros when naming cells/ranges to get the syntax. To name cells/ranges: Versions before xl2007: Menu item Insert - Name - Define "Dragon" wrote: In our excel file we have 11 workbooks with running totals that update daily, weekly and monthly reports in 3 other workbooks. We would like to make up Macros so that the reports will update automatically when run. To do this we need the daily cell reference to move one down during the week, 4 down at week end, and 2 at month end. I think it will take 3 Macros to do this but I have tried to make the reference move down one but it doesn't seem to work. How can I set up Macros to do what I need? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Daily, Weekly and Monthly Reports.
Hi Dragon,
I copied your data to my worksheets and studied it. I now believe that I understand what you are trying to achieve. However, it is a complex issue to create code and be sure that you will always have the correct results because there are too many variables to be controlled and even a minor error by a user in entering data will result in the wrong cells being referenced for the reports. I think that you would be better to change the method of recording the data and then use AutoFilter to produce your Daily, Weekly and Monthly reports. The whole lot can go on one worksheet and the reports are fully dynamic and far less prone to problems that would be created if a macro picks up data from the wrong row. I can give you some more guidance on setting up AutoFilter and then how to set up the worksheet if you want to adopt this method. However, let me know what version of XL you are using because XL2007 has more functionality than earlier versions and I would do it a little differently. The method I would use for earlier versions will still work in xl2007 if you upgrade later. The AutoFilter method will give you Daily, Weekly and Monthly totals including the YTD totals. You can have them on a separate worksheet but I like them on the same worksheet above the actual data because I put the column headers down about row 10 and then freeze the panes from the column headers up and the reports are above the data. This way I do not have to scroll up and down the screen or change worksheets to view reports after setting the filters. Another advantage is that all the reports are always available for any Day, Week or Month just by re-setting the filters. They are not overwritten. By the way your example was great. An example is like a picture. Worth a thousand words. -- Regards, OssieMac "Dragon" wrote: I did up the following sample. Does this help? Sheet1: Date: lbs cases trl: YTD lbs YTD cases YTD trl: 11/26/2007 89450 5640 5 89450 5640 5 11/27/2007 101467 7080 6 190917 12720 11 11/28/2007 45950 1245 2 236867 13965 13 11/29/2007 192658 8023 7 429525 21988 20 11/30/2007 78500 4167 4 508025 26155 24 MONTHLY 508025 26155 24 12/1/2007 102519 7591 6 610544 33746 30 12/2/2007 187662 6482 4 798206 40228 34 Weekly 798206 40228 34 Sheet2: Date: lbs cases trl: cans: rail: YTD lbs YTD cases YTD trl: YTD cans: YTD rail: 11/26/2007 89450 5640 5 0 2 89450 5640 5 0 2 11/27/2007 101467 7080 6 0 2 190917 12720 11 0 4 11/28/2007 45950 1245 2 1 3 236867 13965 13 1 7 11/29/2007 192658 8023 7 1 2 429525 21988 20 2 9 11/30/2007 78500 4167 4 0 3 508025 26155 24 2 12 MONTHLY 508025 26155 24 2 12 12/1/2007 102519 7591 6 2 1 610544 33746 30 4 13 12/2/2007 187662 6482 4 0 2 798206 40228 34 4 15 Weekly 798206 40228 34 4 15 Sheet3: LBS. CS. # TRL LBS. CS. # TRL 11/26/07 518,127 22,479 12 518,127 22,479 12 11/27/07 545,942 26,530 11 1,064,069 49,009 23 11/28/07 302,321 13,633 7 1,366,390 62,642 30 11/29/07 484,423 21,767 13 1,850,813 84,409 43 11/30/07 571,149 24,720 13 2,421,962 109,129 56 MONTHLY 2,421,962 109,129 56 12/01/07 240,656 9,679 8 2,662,618 118,808 64 12/02/07 470,440 20,735 10 3,133,058 139,543 74 WEEKLY 3,133,058 139,543 74 Daily: DAILY REPORT Date: December 2, 2007 LBS. CASES Y.T.D. LBS. Y.T.D. CASES Sheet 1 Sheet1!B9 Sheet1!C9 Sheet1!F9 Sheet1!G9 Sheet 2 Sheet2!B9 Sheet2!C9 Sheet2!H9 Sheet2!I9 Sheet3 Sheet3!B9 Sheet3!C9 Sheet3!E9 Sheet3!F9 TOTAL SUM(E3:E7) SUM(F3:F7) SUM(G3:G7) SUM(H3:H7) # OF RAIL TODAY - Sheet2!F9 # OF RAIL Y.T.D. - Sheet2!L9 DAILY Y.T.D. # OF TRUCKS Sheet1 Sheet1!D9 Sheet1!H9 # OF TRUCKS Sheet2 Sheet2!D9 Sheet2!J9 # OF cans Sheet2 Sheet2!E9 Sheet2!K9 # OF TRUCKS Sheet3 Sheet3!D9 Sheet3!G9 Weekly: Weekly REPORT Week ending: December 2, 2007 LBS. CASES Y.T.D. LBS. Y.T.D. CASES Sheet 1 Sheet1!B10 Sheet1!C10 Daily!G3 Daily!H3 Sheet 2 Sheet2!B10 Sheet2!C10 Daily!G4 Daily!H4 Sheet3 Sheet3!B10 Sheet3!C10 Daily!G6 Daily!H6 TOTAL SUM(E3:E7) SUM(F3:F7) SUM(G3:G7) SUM(H3:H7) # OF RAIL Week - Sheet2!F10 # OF RAIL Y.T.D. - Daily!F10 DAILY Y.T.D. # OF TRUCKS Sheet1 Sheet1!D9 Daily!H11 # OF TRUCKS Sheet2 Sheet2!D9 Daily!H12 # OF cans Sheet2 Sheet2!E9 Daily!H13 # OF TRUCKS Sheet3 Sheet3!D9 Daily!H15 Monthly: Monthly REPORT Date: November, 2007 LBS. CASES Y.T.D. LBS. Y.T.D. CASES Sheet 1 Sheet1!B7 Sheet1!C7 Daily!G3 Daily!H3 Sheet 2 Sheet2!B7 Sheet2!C7 Daily!G4 Daily!H4 Sheet3 Sheet3!B7 Sheet3!C7 Daily!G6 Daily!H6 TOTAL SUM(E3:E7) SUM(F3:F7) SUM(G3:G7) SUM(H3:H7) # OF RAIL Week - Sheet2!F7 # OF RAIL Y.T.D. - Daily!F10 DAILY Y.T.D. # OF TRUCKS Sheet1 Sheet1!D7 Daily!H11 # OF TRUCKS Sheet2 Sheet2!D7 Daily!H12 # OF cans Sheet2 Sheet2!E9 Daily!H13 # OF TRUCKS Sheet3 Sheet3!D7 Daily!H15 "OssieMac" wrote: Perhaps you can you post samples of the data to go with the explanation. I really don't understand what you are trying to achieve. However, naming cells/ranges is a good start in retrieving the last data copied and then using offset to move down and re-naming to the same name ready for the next time. You can record macros when naming cells/ranges to get the syntax. To name cells/ranges: Versions before xl2007: Menu item Insert - Name - Define "Dragon" wrote: In our excel file we have 11 workbooks with running totals that update daily, weekly and monthly reports in 3 other workbooks. We would like to make up Macros so that the reports will update automatically when run. To do this we need the daily cell reference to move one down during the week, 4 down at week end, and 2 at month end. I think it will take 3 Macros to do this but I have tried to make the reference move down one but it doesn't seem to work. How can I set up Macros to do what I need? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros for Daily, Weekly and Monthly Reports.
I'm passing on your suggestion to my collegue, who is partnering me on this,
for their opinion. We are using Office 2000. I will get back to you next week with more information. Thanks, Dragon "OssieMac" wrote: Hi Dragon, I copied your data to my worksheets and studied it. I now believe that I understand what you are trying to achieve. However, it is a complex issue to create code and be sure that you will always have the correct results because there are too many variables to be controlled and even a minor error by a user in entering data will result in the wrong cells being referenced for the reports. I think that you would be better to change the method of recording the data and then use AutoFilter to produce your Daily, Weekly and Monthly reports. The whole lot can go on one worksheet and the reports are fully dynamic and far less prone to problems that would be created if a macro picks up data from the wrong row. I can give you some more guidance on setting up AutoFilter and then how to set up the worksheet if you want to adopt this method. However, let me know what version of XL you are using because XL2007 has more functionality than earlier versions and I would do it a little differently. The method I would use for earlier versions will still work in xl2007 if you upgrade later. The AutoFilter method will give you Daily, Weekly and Monthly totals including the YTD totals. You can have them on a separate worksheet but I like them on the same worksheet above the actual data because I put the column headers down about row 10 and then freeze the panes from the column headers up and the reports are above the data. This way I do not have to scroll up and down the screen or change worksheets to view reports after setting the filters. Another advantage is that all the reports are always available for any Day, Week or Month just by re-setting the filters. They are not overwritten. By the way your example was great. An example is like a picture. Worth a thousand words. -- Regards, OssieMac "OssieMac" wrote: Perhaps you can you post samples of the data to go with the explanation. I really don't understand what you are trying to achieve. However, naming cells/ranges is a good start in retrieving the last data copied and then using offset to move down and re-naming to the same name ready for the next time. You can record macros when naming cells/ranges to get the syntax. To name cells/ranges: Versions before xl2007: Menu item Insert - Name - Define "Dragon" wrote: In our excel file we have 11 workbooks with running totals that update daily, weekly and monthly reports in 3 other workbooks. We would like to make up Macros so that the reports will update automatically when run. To do this we need the daily cell reference to move one down during the week, 4 down at week end, and 2 at month end. I think it will take 3 Macros to do this but I have tried to make the reference move down one but it doesn't seem to work. How can I set up Macros to do what I need? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Daily Reports | Excel Discussion (Misc queries) | |||
formatting daily,weekly and monthly schedules | New Users to Excel | |||
formatting daily,weekly and monthly schedules | New Users to Excel | |||
spreadsheet for tracking reports daily, monthly, yearly and graph | Excel Worksheet Functions | |||
Collecting weekly and monthly totals from daily data | Excel Worksheet Functions |