Copy Pivot table to another sheet and delete all (blanks) values from the cells

[Romano_odK]

Good afternoon,

I got some code and I am trying to delete all the "(blank)" that came with copying of the Pivot table. At the moment I get an error, but when I fill in anothe value, like a number it works. So why can't I delete the (blank) values?

Thank you ffor your time

Romano


Private Sub CommandButton1_Click()
Dim ws As Worksheet


Set ws = Worksheets.Add
ActiveSheet.Name = "Production"
Sheets("Prijslijst").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Selection.copy

ws.Range("A1").PasteSpecial Paste:=xlPasteValues
ws.Range("A1").PasteSpecial Paste:=xlPasteFormats
ws.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths

Sheets("Production").Select
Dim xCel As Range
Set xRange = Range("A:Q")
For Each xCel In xRange
If xCel.Value = "(blank)" Then
xCel.Offset(, 3).ClearContents
End If
Next xCel


End Sub