I need a macro that I will assign to a report button that generates the following report:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
> </o
>
Contract Contract Cost<o
></o
>
2010 2011 2012 2013 2014 totals<o
></o
>
123 $10000 $10000 $10000 $10000 - $40000<o
></o
>
456 - - - - -<o
></o
>
789 - - - - -<o
></o
>
Tot: 10000 $10000 $10000 $10000 - $40000<o
></o
>
<o
> </o
>
Where:<o
></o
>
Current Date is 6/2/2010<o
></o
>
<o
> </o
>
Contract Name 123 <o
></o
>
Contract Expires 7/1/2014<o
></o
>
Contract Cost/Year = $10,000<o
></o
>
Status = Active<o
></o
>
<o
> </o
>
Contract Name 456<o
></o
>
Contract Expires 7/1/2010<o
></o
>
Contract Cost/Year = $1200<o
></o
>
Status = Active<o
></o
>
<o
> </o
>
Contract Name 789<o
></o
>
Contract Expires 8/28/2012<o
></o
>
Contract Cost/Year = $5000<o
></o
>
Status = Suspended<o
></o
>
<o
> </o
>
I have a column which calculates the expiration date (AE)<o
></o
>
I have a field for the status (in example, active, suspended) (AF)<o
></o
>
Payment due every Year, except for last year (Expiration Year minus 1) – See 123 above<o
></o
>
The macro will open a new tab and call it: Payment Forecast”. The tab(worksheet) “Payment Forecast” will delete when the workbook is closed.<o
></o
>
<o
> </o
>
Any suggestions?<o
></o
>
<o
Contract Contract Cost<o
2010 2011 2012 2013 2014 totals<o
123 $10000 $10000 $10000 $10000 - $40000<o
456 - - - - -<o
789 - - - - -<o
Tot: 10000 $10000 $10000 $10000 - $40000<o
<o
Where:<o
Current Date is 6/2/2010<o
<o
Contract Name 123 <o
Contract Expires 7/1/2014<o
Contract Cost/Year = $10,000<o
Status = Active<o
<o
Contract Name 456<o
Contract Expires 7/1/2010<o
Contract Cost/Year = $1200<o
Status = Active<o
<o
Contract Name 789<o
Contract Expires 8/28/2012<o
Contract Cost/Year = $5000<o
Status = Suspended<o
<o
I have a column which calculates the expiration date (AE)<o
I have a field for the status (in example, active, suspended) (AF)<o
Payment due every Year, except for last year (Expiration Year minus 1) – See 123 above<o
The macro will open a new tab and call it: Payment Forecast”. The tab(worksheet) “Payment Forecast” will delete when the workbook is closed.<o
<o
Any suggestions?<o