Rolling a calendar schedule up or down

[Jamie Wagler]

Morning all,

I am trying to put together a schedule and would like to be able to write a macro which will allow me to "roll" my calendar weeks ahead (up or back (down). Below is a skeleton of what I am starting with. Today's date in the top left is a simple =NOW() formula whereas the "Week of" is populated with =$A$1+1-WEEKDAY(TODAY(),2) in the first period and then multiplied by the corresponding period # to increase per period. I realize that this will require 2 MACROS (up and down) but am unfamiliar with the function.

Thanks in advance!

08-Jan-14 Shop Calendar
Period Week of Std Working days
1 05-Jan-14
2 12-Jan-14
3 19-Jan-14
4 26-Jan-14
5 02-Feb-14
6 09-Feb-14
7 16-Feb-14
8 23-Feb-14
9 02-Mar-14
10 09-Mar-14
11 16-Mar-14
12 23-Mar-14
13 30-Mar-14
14 06-Apr-14
15 13-Apr-14
16 20-Apr-14
17 27-Apr-14
18 04-May-14
19 11-May-14
20 18-May-14
21 25-May-14
22 01-Jun-14
23 08-Jun-14
24 15-Jun-14
25 22-Jun-14
26 29-Jun-14

 

[Hui]

If everything is based on the Period No.
Use that to drive the formulas
Where Period 2 is Put =A3+1
Copy down to week 26

Now Add a Spin Control
Link it to cell where Period 1 is and set its Present value to 1
When you press Up/Down Period 1 will go Up/Down
and all the periods below that will change accordingly
As the dates are linked to the period Viola

 

[Jamie Wagler]

Thanks!