View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
dragon dragon is offline
external usenet poster
 
Posts: 4
Default 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?