Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Worksheet Fuctions with Macros
Tom,
Worked like a charm! :) Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
About Fuctions | Excel Worksheet Functions | |||
Using Worksheet Fuctions with Macros | Excel Discussion (Misc queries) | |||
Equalls fuctions | Excel Worksheet Functions | |||
unrecognized fuctions evapp etc | Excel Worksheet Functions | |||
just copy the data, not the fuctions | Excel Programming |