looping through sheets based on range

[kocab]

Hi
I pretty new to VBA and have a problem I need some help solving. I have one sheet with a range (MyRange) containing the sheet names I would like to loop through. If the sheet name matches the name in MyRange I would like the macro to create a pivottable on a sheet that already is created and named name in MyRange & Report. The code I’ve come up with and that have gotten me nowhere is as follows:

Option Explicit
Sub RawData9_CreatePivots()

Dim MyCell As Range, MyRange As Range, ws As Worksheet, Arr() As Variant, wsName As Variant

Application.ScreenUpdating = False

Set MyRange = Sheets("data").Range("k1")

Set MyRange = Range(MyRange, MyRange.End(xlDown))

Set wsName = MyRange

Arr = Array(wsName)

For Each ws In ThisWorkbook.Worksheets

For Each wsName In Arr​

If wsName = ws.name Then​

ActiveSheet.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:=Worksheets(wsName & " report")​

End If​

Next wsName​

Next ws

End Sub

‘Any help or clue is welcome.
‘Much appreaciated

 

[AFPathfinder]

I think this might get you a little closer. It hasn't been tested, so some debugging will probably be needed. You were missing the Range value in the TableDestination property, so the "Range("A" & n).Value" is a placeholder for now.

Option Explicit
Sub RawData9_CreatePivots()
    Dim finalRow As Integer
    Dim n As Integer
    Dim MyCell As Range
    Dim MyRange As Range
    Dim ws As Worksheet
    'Dim Arr() As Variant
    'Dim wsName As Variant

    Application.ScreenUpdating = False
    finalRow = Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
    
    Set MyRange = Sheets("data").Range("K1:K" & finalRow)
    'Set wsName = MyRange
    'Arr = Array(wsName)
    
    n = 1
    
    For Each ws In ThisWorkbook.Worksheets
        For Each MyCell In MyRange
        
            If MyCell.Value = ws.Name Then
            
                ActiveSheet.PivotCaches.Add(SourceType:=xlDatabase, _
                    SourceData:=Range("A1").CurrentRegion).CreatePivotTable _
                    TableDestination:=Worksheets(wsName & " report").Range("A" & n).Value
                n = n + 20
            
            End If
        Next MyCell
    Next ws
    
    Set MyRange = Nothing
    
    Application.ScreenUpdating = True
End Sub

I went ahead and commented out the array and wsName since I don't believe you'll need them. I'm not an expert on arrays, but I think your array was being defined as a Range instead of a String for name comparison.