Copy and paste pivot table results using a macro

Question

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

Answer 1

Based on your attached image, the code is copying cells C22 and D22, both of which are #DIV/0!, to cells F1, F2, and F3 - so it is working correctly in copying that value to those cells.

Answer 2

The problem being as the macro runs swapping out fields in the pivot table there are values that appear and disappear. As they appear, they are suppose to be copied and pasted into the applicable destination cells. What you see in the screen shot is what it looks like after it runs.

Answer 3

The way the code is set up is to always put whatever ends up as the last value in cells C22 and D22 into F1, F2, and F3. That's why the end result is the #DIV/0.

Answer 4

How do I make retain the value that's pasted after the switch? Is it something in the VBA or within the app itself?

Answer 5

Are you able to save the file to OneDrive and post the link here? I can then take a look.