Help Trying to edit an excel macro that will change to previous week automatically?

Question

Creating Weekly Bar Graphs. Do not want to keep going in each week and change start and end date of each week every time. I know how to edit the macro. I need to know what function I put in so that the macro will automatically change the start and end date to the prev. week.
Example: If I ran the maco today
The title of the Bar Graph would display 8/12/07-08/18/07. Any help would be appreciated.

Answer 1

It's much easier to do this without a macro. Use a dynamic named range as the data source and have the named range expand automatically.

If your data is in a column and the data has the latest dates at the bottom, use the following formula for the named range reference:
=OFFSET($A$1,COUNTA($A:$A) -7,,7,1)

For data in a row format use:
=OFFSET($A$1,,COUNTA($A:$A) -7,7,1)

Change $A$1 to be the first cell of your row/column

Answer 2

Here's how I created the formulas for an Excel macro that keeps track of my work hours and work periods..

Dim Today As Date
Dim RefDate As Date
Dim Difference As Double
Dim PayPeriods As Double
Dim PayPeriodStart As Date
Dim PayPeriodEnd As Date

Today = Format$(Now, "m/d/yy")
RefDate = Format$("5/1/06", "m/d/yy")
Difference = Today - RefDate
PayPeriods = Int(Difference / 14)
PayPeriodStart = RefDate + (PayPeriods * 14)
PayPeriodEnd = PayPeriodStart + 13

The second line I used for a reference date to figure out the beginning of my two week pay period. You can use the same type of formulas, just you'd probably substitute in your own reference date, replace 14 with 7 and replace 13 with 6.

If you need to figure out how to change the chart title, you can use the Macro Recorder to help you figure that out by recording a macro as you change the chart title then going back and looking at the code.