title: Excel VBA Pivot Table -- Formatting Numbers as Percents without moving Decimal
body: <p>I want to convert values in my Pivot Table to "0.0%" format without moving the decimal, e.g. 1 --> 1%, NOT 100%.</p>
  
  <p>The code I'm using produces a  "Run-time error '1004': Cannot change this part of a PivotTable report" error in my VBA pivot table macro. The code is:</p>
  
  <pre><code>Sub Cost_Pivot()
   NoOfOutputRows = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
   NoOfCols = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R" &amp; NoOfOutputRows &amp; "C" &amp; NoOfCols, Version:=xlPivotTableVersion12).CreatePivotTable _
  TableDestination:="Sheet2!R1C1", TableName:="PivotTable1", DefaultVersion _
   :=xlPivotTableVersion12
   Sheets("Sheet2").Select
   Cells(1, 1).Select
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("Metric")
   .Orientation = xlRowField
   .Position = 1
   End With
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("CAM")
   .Orientation = xlRowField
   .Position = 2
   End With
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("CAM")
   .Orientation = xlColumnField
   .Position = 1
   End With
   ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Quantification"), "Sum of Quantification", xlSum
   Range("A2").Select
   ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Metric"
   Range("B1").Select
   ActiveSheet.PivotTables("PivotTable1").CompactLayoutColumnHeader = "CAM"
  ActiveSheet.PivotTables("PivotTable1").ColumnGrand = False
  For Each ce In ActiveSheet.Range(Cells(1, 1), Cells(100, 100))
  If IsNumeric(ce.Value) = True Then
  ce.Value = ce.Value * 0.01
  End If
  Next ce
   With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Quantification")
   .NumberFormat = "0.0%"
   End With
  End Sub
  </code></pre>
  
  <p>The error appearing is at the ce.Value = ce.Value * .01 where I want to multiply the cell values by .01 before converting them to a percent number format. I read <a href="http://blog.contextures.com/archives/2011/03/16/change-values-in-a-pivot-table/" rel="nofollow">this</a> article saying that I have to create a calculated value, but the article isn't VBA related and I don't know how to create a calculated value in a macro.</p>
  
  <p>Is there a better solution?</p>