If cell contains specific formula (GETPIVOTDATA)

A

A. Karatas

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
 
S

smartin

A. Karatas said:
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
 
A

A. Karatas

A.Karataswrote:





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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top