Worksheet has multiple pivot tables -Unable to get the PivotFields property of the PivotTable class.

[Mithril28]

I know there are 18 million posts about this error, but I have not found anything that works with multiple pivot tables


I have a worksheet with multiple pivot tables named PivotTable1, PivotTable 2 etc.
Each has a date filter, the field for every pivot table is named date, each has the same set of dates
Every week this spreadsheet is updated with another set of data for the previous week, and the customer needs the date filter to have the latest three dates selected.
So after the spreadsheet is refreshed I want the macro to deselect the 4th oldest date, and select the newest date
But! Since it has multiple pivot tables, it fails on the bolded code below.
Any suggestions welcome. My brain hurts!

It is not my spreadsheet so I can't modify anything about it.

Sub SetDates()
    Dim St As Worksheet
    Dim NewSt As Worksheet
    Dim pt As PivotTable
    Application.ScreenUpdating = False
    ThisWorkbook.RefreshAll
    With NewSt
     With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        For Each St In ActiveWorkbook.Worksheets
            For Each pt In St.PivotTables
    pt.PivotFields("Date").PivotItems("8/08/2016").Visible = False
    pt.PivotFields("Date").PivotItems("8/29/2016").Visible = True
            Next
        Next
        End With
        End With
    Application.ScreenUpdating = True
End Sub

[Micron]

Colored text might work between code tags but bolding doesn't. Can't tell which line is generating the error.
Try color or 'comment on the line.

Each has a date filter, the field for every pivot table is named date, each has the same set of dates
Every week this spreadsheet is updated with another set of data for the previous week, and the customer needs the date filter to have the latest three dates selected.

I don't get where all these dates are coming from. If each pt has the same dates, where are the "latest three dates" coming from. Selected from where, the pt? The spreadsheet that the pt is on?
EDIT: after re-reading, this isn't Automation from Access as I first thought. It looks like it's purely Excel, but you are posting vba code in an Access forum in the Macros section?

[ItsMe]

I think I see
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Date")

But, I don't see as well as I used to. Because of this, I have crazy settings on my monitors and graphics.

[Micron]

You see bold font in that code? I don't. It's all the same, like Courier, perhaps, with no formatting.

[CJ_London]

Since it has multiple pivot tables, it fails on the bolded code below.

the bolded line is

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Date")


but my question is - what does 'fail' mean? What error do you get?

Also be aware that Date is a reserved word (in vba it means Today) and using reserved words can generate unexpected and misleading errors

[Micron]

I wondered about "Date" as well, but figured it might be OK since it was in quotes. I suspect the main issue is that the worksheets and pivot table have been declared but not SET to anything. Then again, it may be that the ActiveSheet has no pivot table when this starts.

But to me, it all looks weird, like the OP is trying to work with a worksheet nested on a worksheet pivot table (impossible?), and is then trying to loop through a set of worksheets from there. I thought the idea was to loop through pivot tables on worksheets, but if not, it still seems problematic to attempt to access a worksheet on a worksheet.

With NewSt <-- this has not been set
     With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
          For Each St In ActiveWorkbook.Worksheets<--St is a worksheet object (not SET) but is not a member of the ActiveSheet anyway.

And what looks like an attempt to affect two different pivot tables

pt.PivotFields("Date").PivotItems("8/08/2016").Visible = False
pt.PivotFields("Date").PivotItems("8/29/2016").Visible = True

is really the same one since this is being done in a loop
For Each pt In St.PivotTables

Next

BTW, I'm still curious as to why I don't see bolded code. I'm using Firefox 48.0.2

[Mithril28]

Oh My GOSH!!! I don't know what to say except to apologize. My question is about an excel macro and this is indeed an Access forum. Sigh. I'll post to an excel forum.

[ItsMe]

...
BTW, I'm still curious as to why I don't see bolded code. I'm using Firefox 48.0.2

I am using I.E. What happens if you copy the text and paste it somewhere else? Does it retain the font style? I would guess it is simply not readily apparent. I can't imagine the browser not wanting to render the HTML correctly.

[Micron]

Courier 10 when pasted into Word - no formatting. Must be my monitor, because this is what it looks like in FF


and in IE 11

There is a very slight bolding with IE, but if I wasn't told it was bold, I'd never see it. Probably doesn't show up in a picture.

[ItsMe]

Yup, that's the font how I saw/see it (the IE version you posted). The only difference for me is the edges of each character are crisp, to the point I can tell it is larger and darker.
.