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)

CodePudding user response：

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.