Compile Error - Expected Array while updating pivot

Question

I'm trying to automate a pivot table to it uses the lowest value of a column as the filter for the pivot.

The value in Cell H6 is what will be updating the filter for the pivot, but I need this value to be dynamic and change to the lowest value in column W, However, the code doesn't work with a formula in the cell as it's not viewing it as a value.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("cumulative sales pivot").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Issue Day Of Sale ID")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Range("H6").Select
    Range("H6").PasteSpecial xlPasteFormulas("=MIN(W:W)")
    Range("H6").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

The first part the code works but the second part is giving me

"Compile Error - Expected Array"

Any ideas where i'm going wrong?

`Range("H6").Value = Application.Min(Range("W:W"))`? It looks like that's what you're trying to do in those couple lines. — BigBen, Feb 21 '20 at 14:47
[Avoid .select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) where possible. — Cyril, Feb 21 '20 at 15:19
Assuming `Range("H6").PasteSpecial xlPasteFormulas("=MIN(W:W)")` is there error line, your `xlPasteFormulas` refers to pasting a formula from the source cell, which does not include the formula to be pasted; that is not the same as `Range("H6").Formula = "=Min(W:W)"` which appends a formula to a cell. You would receive a compile error since the syntax as written would not be able to be compiled. [See PasteSpecial documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial). — Cyril, Feb 21 '20 at 15:25

score 0 · Answer 1 · answered Feb 21 '20 at 15:36

Typing out comment as an answer. Note that BigBen's comment would resolve the issue, so capturing that here, as well:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Range("H6").Value = Application.Min(Range("W:W"))
End Sub

Now to your compile error:

The compile error occurring at Range("H6").PasteSpecial xlPasteFormulas("=MIN(W:W)") is due to syntax.

Within the documentation for Range.PasteSpecial you will notice that the arguments are for action of pasting.

As writting, the ("=MIN(W:W)") tacked onto the xlPasteFormulas would flag the error, where the compiler is seeing the () as being applicable to an array. That is why you got the error you received.

Now, onto appropriate use of Range.PasteSpecial... Range.PasteSpecial (Paste, Operation, SkipBlanks, Transpose) has 4 possible arguments. I will leave the further explanation to the documentation about what can fill each argument, but will give an example of a couple Paste arguments which are common:

'Pastes the value from the copied cell
Cells(1,1).Copy
Cells(2,2).PasteSpecial xlPasteValues

'Pastes the formula from the copied cell
Cells(1,1).Copy
Cells(2,2).PasteSpecial xlPasteFormulas

Note that in the above, both require that the formula or value comes from the copied cell.

If you want to append a formula to a cell, you can use Range.Formula, such that:

Cells(1,1).Formula = "=Min(B:B)"

Hopefully that both explains the issue and resolves your problem.

Compile Error - Expected Array while updating pivot

1 Answers1