mismatch error, pivot build automation, multisheets specific

[Shiro26]

Hi,
I am trying to create a pivot table on specific sheets.

Initially, I had hardcoded the name of my sheets in the below line
myarray = Array("ws1","ws2",etc)

But now that I have created a dynamic named range, it does not work anymore.

The problem returned at this point is a 'mismatched on the line

Set PTOutput = Sheets(pvtname)


Is there anybody who can tell me what I am not able to see to cope with that issue?
How can I improve the code I wrote below?

Thank you in advance for your help.


Note:
if 'MsgBox (myarray(i)) is not put as a comment, I also have a mismatch error on that line

Sub BuildPvt1020Loop_question()
Application.ScreenUpdating = False
Dim pt As PivotTable
Dim wst As Worksheet
Set wst = Worksheets("DataTable")
Dim PTCache As PivotCache
Dim PRange As Range
Dim PTOutput As Worksheet
Dim i As Integer
Dim myarray As Variant
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("myData"))
myarray = Array(Range("List1"))
    For i = LBound(myarray) To UBound(myarray)
        'MsgBox (myarray(i))
        pvtname = (myarray(i))
        Set PTOutput = Sheets(pvtname)
        
        PTOutput.Range("20:100").EntireColumn.Delete
        
        ' Create the pivot table
        Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(10, 20), TableName:=pvtname)
            pt.ManualUpdate = True
            pt.AddFields RowFields:="RowLabels", ColumnFields:="ColLabels", PageFields:="ID"
            With pt.PivotFields("DataValues")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 1
            End With
            pt.PivotFields("ID").CurrentPage = (myarray(i))
            pt.ManualUpdate = False
    Next i
        
Erase myarray
Application.ScreenUpdating = True
End Sub

Best,
Shiro

 

[sericom]

Might be easier if you don't use arrays, eg

For Each c In Range("List1")
    MsgBox c.Value
    pvtname = c.Value
    
    'rest of code here
    
Next

 

[sericom]

If you did want to use arrays, then here's an example

Dim myarray() As Variant
myarray = Range("List1")
MsgBox myarray(3, 1)

If List1 was Range("A1:A10"), then that would list the value in A3.
Assigning a range to an array creates a 2 dimensional array.

 

[Shiro26]

If you did want to use arrays, then here's an example

Dim myarray() As Variant
myarray = Range("List1")
MsgBox myarray(3, 1)

If List1 was Range("A1:A10"), then that would list the value in A3.
Assigning a range to an array creates a 2 dimensional array.

Hello,

Thank you for your input. This makes sens.
I am going to try and will post again if I face another issue.
Thank you again

Best,

Shiro

 

[Shiro26]

Hi,

So as you recommended I have changed the code to

Sub BuildPvt1020Loop_question()
Application.ScreenUpdating = False
Dim pt As PivotTable
Dim wst As Worksheet
Set wst = Worksheets("DataTable")
Dim PTCache As PivotCache
Dim PRange As Range
Dim PTOutput As Worksheet
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("myData"))
    For each c in Range("List1")
        pvtname = c.value
        Set PTOutput = Sheets(pvtname)
        
        PTOutput.Range("20:100").EntireColumn.Delete
        
        ' Create the pivot table
        Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(10, 20), TableName:=pvtname)
            pt.ManualUpdate = True
            pt.AddFields RowFields:="RowLabels", ColumnFields:="ColLabels", PageFields:="ID"
            With pt.PivotFields("DataValues")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 1
            End With
            pt.PivotFields("ID").CurrentPage = c.value
            pt.ManualUpdate = False
    Next c
        
Application.ScreenUpdating = True
End Sub

However, now I have an error message of overflow on the line Set PTOutput = Worksheets(pvtname)
Do you have any idea why is that so by any chance?

Should I dim the 'pvtname' and the 'c' as well?


Kind Regards,

Shiro