Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell contains specific formula (GETPIVOTDATA)
Hi,
I have a masterfile, which imports data from various other files (that needs to be openened to refresh). After gathering the data some of the sheets are send out to the responsible persons who have to fill in data. In the masterfile I have various sheets with numerous cells that contains formulas (for example sheet P&L or Sales). Because some cells contains formulas that have the GETPIVOTDATA formula, I want to build a macro that searches for these cells and copy paste these formulas as hard values after refreshing all data and files (and before sending it out). EXCEL 2007 is used by me. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell contains specific formula (GETPIVOTDATA)
A. Karatas wrote:
Hi, I have a masterfile, which imports data from various other files (that needs to be openened to refresh). After gathering the data some of the sheets are send out to the responsible persons who have to fill in data. In the masterfile I have various sheets with numerous cells that contains formulas (for example sheet P&L or Sales). Because some cells contains formulas that have the GETPIVOTDATA formula, I want to build a macro that searches for these cells and copy paste these formulas as hard values after refreshing all data and files (and before sending it out). EXCEL 2007 is used by me. Thanks in advance This works in Excel 2003. I'm sure it can be improved upon... Sub test() Dim c As Range For Each c In ActiveSheet.UsedRange 'Debug.Print c.Address, c.Formula If c.Formula Like "=GETPIVOTDATA*" Then c.Copy c.PasteSpecial xlPasteValues End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If cell contains specific formula (GETPIVOTDATA)
On Aug 7, 12:18*am, smartin wrote:
A.Karataswrote: Hi, I have a masterfile, which imports data from various other files (that needs to be openened to refresh). After gathering the data some of the sheets are send out to the responsible persons who have to fill in data. In the masterfile I have various sheets with numerous cells that contains formulas (for example sheet P&L or Sales). Because some cells contains formulas that have the GETPIVOTDATA formula, I want to build a macro that searches for these cells and copy paste these formulas as hard values after refreshing all data and files (and before sending it out). EXCEL 2007 is used by me. Thanks in advance This works in Excel 2003. I'm sure it can be improved upon... Sub test() * * *Dim c As Range * * *For Each c In ActiveSheet.UsedRange * * * * *'Debug.Print c.Address, c.Formula * * * * *If c.Formula Like "=GETPIVOTDATA*" Then * * * * * * *c.Copy * * * * * * *c.PasteSpecial xlPasteValues * * * * *End If * * *Next End Sub- Hide quoted text - - Show quoted text - Works great. Thankx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to translate a getpivotdata formula into a cell reference | Excel Discussion (Misc queries) | |||
Edit a GETPIVOTDATA formula? | Excel Programming | |||
GetPivotData from OLAP Cube Cell Referencing Formula. | Excel Worksheet Functions | |||
GETPIVOTDATA FORMULA | Excel Worksheet Functions | |||
Using a cell ref for the data_field arg in a GETPIVOTDATA formula | Excel Programming |