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

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

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