Using Worksheet Fuctions with Macros

D

douglascfast

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?
 
Top