Run Time Error 1004 Macro conflict with Pivot Table

[Risk]

The code below errors when I have a file with Pivot Tables. Does anybody have any ideas for a work around or correction?

Its erroring at

z.Copy
z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,

with the message

Run-time error '1004':

You cannot change, move a part of, or insert cells in a Pivot Table report. If you want to insert cells on the worksheet, drag the entire report out of the way. To move or add data to a report, do one of the following:
- Use the PivotTable and PivotChart Wizard to add fields to the report.
- Hide or group items in a row or column field.



Sub ValueHPAll()
TxtMsg = "You have selected to value all Hyperion formula's in this workbook. If you wish to proceed please select OK"
y = MsgBox(TxtMsg, vbOKCancel, "Proceeding with valuing Hyperion formula's.")

Application.ScreenUpdating = False 'turn off screen updating

If y = 1 Then

Dim sh As Worksheet, HidShts As New Collection
For Each sh In ActiveWorkbook.Worksheets
If Not sh.Visible Then
HidShts.Add sh
sh.Visible = xlSheetVisible
End If
Next sh

For Each x In Worksheets
Sheets(x.Name).Activate
Range("a1").Select
ActiveCell.SpecialCells(xlLastCell).Select
LastCell = ActiveCell.Address
Range("a1:" & LastCell).Select

For Each z In Selection
If InStr(1, z.FormulaR1C1, "HPLNK") = 0 Then
If InStr(1, z.FormulaR1C1, "HP") > 0 Then
z.Copy
z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next z
Range("a1").Select
Next x
Application.ScreenUpdating = True 'refresh the screen

For Each sh In HidShts
sh.Visible = xlSheetHidden
Next sh

Else
MsgBox "You have chosen to cancel this process"

End If

End Sub


Thanks for the help

-Risk

 

[Andrew Poulsom]

The FormulaR1C1 property returns the text in a cell if it doesn't have a formula (ie doesn't start with =). So you need to check that the cell actually has a formula.

Either select only the formula cells:

Range("A1:" & LastCell).SpecialCells(xlCellTypeFormulas).Select

Or check that the cell has a formula:

If z.HasFormula Then
   z.Copy 
   z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
End If

 

[Risk]

Andrew,

Works great! I opted for the "check that the cell has a formula".

If z.HasFormula Then
   z.Copy
   z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

Its amazing how much I've learned from this community in only 14 posts! I actually think I owe Andrew lunch since he's helped me twice...thanks btw.

-Risk