VBA Problem with pivot table Object Doesn't Support this Property or Method

Question

I have a macro that automatically opens a pivot table file when I press YES. And I would like it to automatically change the data source from the next and other open file. However, I get the Object Doesn't Support this Property or Method error here: And file is opening normally.

PvtKraje.ChangePivotCache ActiveWorkbook. _
       PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wbMe.Sheets("corep"), _
       Version:=6)

strPath = selectFile
If strPath = "" Then Exit Sub
Set wbMe = Workbooks.Open(strPath, UpdateLinks:=0)
Dim PvtKraje As PivotTable
Dim data_wb As Workbook
CarryOn = MsgBox("Are you would like open BHPKO_mm_yyyy_podział po krajach?", vbYesNo, "Czy kontynuować?")
If CarryOn = vbYes Then
MyFolder = "source1"
MyFile = Dir(MyFolder & "\name_file1*.xlsx")
If MyFile <> "" Then

Set data_wb = Workbooks.Open(MyFolder & "\" & MyFile, UpdateLinks:=0)
Set PvtKraje = data_wb.Sheets("PODZIAŁ_KRAJ").PivotTables("KRAJE")
PvtKraje.ChangePivotCache ActiveWorkbook. _
       PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wbMe.Sheets("corep"), _
       Version:=6)

SourceData:=wbMe.Sheets("corep") - this looks off. Maybe SourceData:=wbMe.Sheets("corep").UsedRange? — BigBen
– BigBen, Commented Feb 16, 2022 at 14:35
@BigBen You are best ! its working! why we should add used range ? — Przemek Dabek
– Przemek Dabek, Commented Feb 16, 2022 at 14:56

BigBen · Accepted Answer · 2022-02-16 15:00:19Z

1

The issue is here:

SourceData:=wbMe.Sheets("corep")

From the PivotCaches.Create docs:

The SourceData argument ... should be passed a Range object (when SourceType is either xlConsolidation or xlDatabase)

You're passing a Worksheet currently, but need a Range:

SourceData:=wbMe.Sheets("corep").UsedRange

A side note, from the following paragraph in the docs:

When passing a Range object, we recommend that you either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly.

In other words, UsedRange may work, but you might also encounter an unexpected Type Mismatch. You could either use a string, or set up a named range, or cross your fingers and hope for the best with UsedRange.

answered Feb 16, 2022 at 15:00

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Problem with pivot table Object Doesn't Support this Property or Method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related