This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
This a follow up to a previous question. I have recorded a macro that is supposed to swap out pivot table fields and along the way copy and paste values from one cell to another e.g. copy from cell D22 and paste in F1, copy from cell D22 and paste in F2, and so on. While it looks fine during record, in practice it leaves #DIV/0! instead of the copy and pasted value. Provided is a screenshot and the code.
Sub Run_Pivot()
'
' Run_Pivot Macro
'
'
Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Question 11")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Answer 11")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Answer 11"), "Count of Answer 11", xlCount
Range("D22").Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Answer 11"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Answer 11").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Question 11").Orientation _
= xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Question 12")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Answer 12")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Answer 12"), "Count of Answer 12", xlCount
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Answer 12"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Answer 12").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Question 12").Orientation _
= xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Question 13")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Answer 13")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Answer 13"), "Count of Answer 13", xlCount
Range("C22").Select
Application.CutCopyMode = False
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Answer 13"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Answer 13").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("Question 13").Orientation _
= xlHidden
Range("C1").Select
End Sub
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
I saved a copy of the mock up I did on my home computer using Excel 2016 works as intended. It also worked on my work computer using Excel 2013 when I was on a Windows 7 computer, but doesn't work on my new Windows 10 work computer. In any case, here's the link...I think.
Update. If I do anything involving the data be it refresh, connections, change data source etc. Excel locks up with Loading Data Model in the lower right hand corner. Same thing happens if I run the macro. Again, everything works on my home computer using Excel 2016, but not on my work computer using Excel 2013. Both systems have Windows 10 albeit I have home edition at home and enterprise at work. And I just noticed that Excel 2013 is 32 bit while Windows 10 is 64 bit where as at home both Excel and Windows 10 are 64. Not sure this last part matters, just trying to cover as many variables as I can think of.