0

I am attempting to loop though the first column on a worksheet and use those values as report filters for a pivot table on another worksheet. However, I am getting

run-time error 1004 "Application-defined or object-defined error"

when I run the following code.

Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer
Dim ThisItem As String
Dim pt As PivotTable

RowCount = 0

Set pt = Worksheets("MySheet").PivotTables("MyPivot")

Set sh = Worksheets("LoopSource")

For Each rw In sh.Rows

    If sh.Cells(rw.Row, 1).Value = "" Then
        Exit For
        Else:
            ' Set the current item name as a variable
            ThisItem = sh.Cells(rw.Row, 1).Value

            ' Filter the pivot table on the value
            With pt
                .PivotFields("FilterField").ClearAllFilters
                .PivotFields("FilterField").CurrentPage = ThisItem
                .RefreshTable
            End With

            ' Print the pivot table to PDF
            Call Module1.PrintPDF(ThisItem)

    End If

    RowCount = RowCount + 1

Next rw

It fails on this line:

    .PivotFields("FilterField").CurrentPage = ThisItem

If I hard-code a value like this it works:

    .PivotFields("FilterField").CurrentPage = "Criterion"

Likewise, it also works if instead of setting ThisItem to the cell value, I do something like

ThisItem = "Criterion"

I do not understand the reason for the error, since ThisItem is a string variable. What am I missing? Thank you for your help.

sprinke
  • 1
  • 1
  • Where is `ThisItem` declared? You declared `ThisCourse`. Also, there are simpler ways to loop from the first row to the last instead of the method you are using. What is the value of `ThisItem` when it fails? – BigBen Sep 14 '19 at 00:17
  • Sorry, I was trying to sanitize the code and I missed that. Where it says ThisCourse it should be ThisItem. To answer your question, it fails in the first iteration of the loop. It assigns the correct value for ThisItem (verified by displaying a MsgBox). If you are looking for the exact value, is it a string "Ma 001A". – sprinke Sep 14 '19 at 01:17
  • What is the value of `ThisItem` when the error occurs. Step though the code to find that – Siddharth Rout Sep 14 '19 at 01:22
  • If I edit the With block as follows: ` With pt .PivotFields("FilterField").ClearAllFilters MsgBox(ThisItem) .PivotFields("FilterField").CurrentPage = ThisItem .RefreshTable End With` the value that I expect is displayed in the message box; it is a string "Ma 001A". – sprinke Sep 14 '19 at 01:42
  • Also @BigBen I would love to see other ways to loop if you have the time to explain. – sprinke Sep 14 '19 at 02:04
  • Oh man, I found the source of my error. The resulting pivot table has no values. So I have to figure out how to error handle that situation. – sprinke Sep 14 '19 at 02:11
  • To loop - see [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. Then all you need is `Dim i as Long`, then a `For i = 1 to lastRow`. Within the loop, `ThisItem = sh.Cells(i, 1).Value`. Should be straightfoward but let me know if you need more detail. This is simpler than `If sh.Cells(rw.Row, 1).Value = "" Then... Exit Sub`. – BigBen Sep 14 '19 at 02:24

0 Answers0