Run Time Error 1004 Macro conflict with Pivot Table

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

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

Or check that the cell has a formula:

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

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
Andrew,

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

Code:
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
 

Forum statistics

Threads
1,141,681
Messages
5,707,795
Members
421,528
Latest member
datdude151

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top