Using Worksheet Fuctions with Macros

I posted this message to MISC but got no response, so maybe this is a
better place:

All,

I have spent a few days looking for this answer and now will post in
hopes some on can help.

Using Excel 2000

1) Have a macro which copies headers and custom formulas to the left of
a pivot table (this works fine when run by its self) Oh yeah if you
see a better way to do this then let me know
=======================================================
Sub Add_formulas()

Range("J13").formular1c1 = "Share LY"
--3 more same as above with different titles

Range('J14").formular1c1 = "=IF(rc[-4]=0,""-"",rc[-6]/rc[-1])'
--3 more same as above with different math

Dim RW as integer
RW =
Worksheets('Corporate_Tool").PivotTables("Main").Databodyrange.rows.count
--How many rows are in the pivot table that need the formula copied
down

Range(J14:M14).Select 'The formulas entered above
Selection.copy
Range(J14:m" & Rw + 13).Select
Activesheet.paste

Range("a1").select

End sub
======================================================

I want to run this macro every time someone changes the selection of
the pivot table.

When I tried
==========================================
Private Sub wroksheet_calculate

Call Add_Formulas

End sub
============================================
Excel runs until it tells me my copy and paste area are not the same.


Any ideas?

Private Sub worksheet_calculate()
On error goto ErrHandler
Application.EnableEvents = False
Call Add_Formulas
ErrHandler:
Application.EnableEvents = True
End sub

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
>I posted this message to MISC but got no response, so maybe this is a
> better place:
>
> All,
>
> I have spent a few days looking for this answer and now will post in
> hopes some on can help.
>
> Using Excel 2000
>
> 1) Have a macro which copies headers and custom formulas to the left of
> a pivot table (this works fine when run by its self) Oh yeah if you
> see a better way to do this then let me know
> =======================================================
> Sub Add_formulas()
>
> Range("J13").formular1c1 = "Share LY"
> --3 more same as above with different titles
>
> Range('J14").formular1c1 = "=IF(rc[-4]=0,""-"",rc[-6]/rc[-1])'
> --3 more same as above with different math
>
> Dim RW as integer
> RW =
> Worksheets('Corporate_Tool").PivotTables("Main").Databodyrange.rows.count
> --How many rows are in the pivot table that need the formula copied
> down
>
> Range(J14:M14).Select 'The formulas entered above
> Selection.copy
> Range(J14:m" & Rw + 13).Select
> Activesheet.paste
>
> Range("a1").select
>
> End sub
> ======================================================
>
> I want to run this macro every time someone changes the selection of
> the pivot table.
>
> When I tried
> ==========================================
> Private Sub wroksheet_calculate
>
> Call Add_Formulas
>
> End sub
> ============================================
> Excel runs until it tells me my copy and paste area are not the same.
>
>
> Any ideas?
>

Tom,

Worked like a charm!



Doug