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").D atabodyrange.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?

Using Worksheet Fuctions with Macros
 

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

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
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").D atabodyrange.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?

Using Worksheet Fuctions with Macros
 

Tom,

Worked like a charm!

:)

Doug