VBA Dynamic PivotTable Name

[th259]

I have the following code that show details of a filtered PivotTable in new worksheets. I want to create new PivotTable in these new worksheets and have the following code but I keep on running into the object required error on the Set Tbl = d.Parent.Cells(d.Row, lCol).Name line? How do I fix that error?

Dim lCol As Long
    Dim d As Range
    Dim sRow As String
    sRow = "Vendor Name"
    Set d = Worksheets("various1").PivotTables("multiple_lines").DataBodyRange.Resize(, 1).SpecialCells(xlCellTypeVisible)
    With Worksheets("various1").PivotTables("multiple_lines")
        With .RowRange
            On Error Resume Next
            lCol = WorksheetFunction.Match(sRow, .Resize(1), 1)
            On Error GoTo 0
            If lCol = 0 Then
                MsgBox "Rowfield Header: " & sRow & "not found."
                Exit Sub
            End If
            lCol = .Column + lCol - 1
        End With
        For Each d In Worksheets("various1").PivotTables("multiple_lines").DataBodyRange.Resize(, 1).SpecialCells(xlCellTypeVisible)
           If d.Parent.Cells(d.Row, lCol) <> "Grand Total" Then
                d.ShowDetail = True
                ActiveSheet.Name = Trim(Left(.Parent.Cells(d.Row, lCol), 20))
           End If
    ' Declare Variables
    Dim FSheet As Worksheet
    Dim GSheet As Worksheet
    Dim FCache As PivotCache
    Dim FTable As PivotTable
    Dim pvtFld As PivotField
    Dim FRange As Range
    Dim LastRowC As Long
    Dim LastColC As Long
    Dim Tbl As String
' Declare Variables
    Set FSheet = ActiveSheet
    Set GSheet = ActiveSheet
    Set Tbl = d.Parent.Cells(d.Row, lCol).Name
' Define Data Range
    LastRowC = GSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastColC = GSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set FRange = GSheet.Cells(1, 1).Resize(LastRowC, LastColC)
' Define Pivot Cache
    Set FCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=FRange). _
    CreatePivotTable(TableDestination:=FSheet.Cells(1, 12), _
    TableName:=Tbl)
' Insert Blank Pivot Table
    Set FTable = FCache.CreatePivotTable _
    (TableDestination:=FSheet.Cells(1, 12), TableName:=Tbl)
With ActiveSheet.PivotTables(Tbl)
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
End With
With FTable
    For Each pvtFld In .PivotFields
        pvtFld.Subtotals(1) = False
    Next pvtFld
End With
' Insert Row Fields
    With ActiveSheet.Name = ActiveSheet.PivotTables(Tbl).PivotFields("Check#")
    .Orientation = xlRowField
    .Position = 1
    End With
     With ActiveSheet.Name = ActiveSheet.PivotTables(Tbl).PivotFields("Object")
    .Orientation = xlRowField
    .Position = 2
    End With
     With ActiveSheet.Name = ActiveSheet.PivotTables(Tbl).PivotFields("Object Name")
    .Orientation = xlRowField
    .Position = 3
    End With
' Insert Data Field
     ActiveSheet.PivotTables(Tbl).AddDataField ActiveSheet.PivotTables( _
        .Parent.Cells(d.Row, lCol)).PivotFields("Total"), "Sum of Total", xlSum
        Next d
    End With

 

[Micron]

You Set object variables, not string or other variable types. I believe your code is looking for an object variable named Tbl and you didn't declare it as such.
So just Tbl = d.Parent.Cells(d.Row, lCol).Name

 

[th259]

I still get the Run-time error '1004: Application-defined or object-defined error. If I set it up as below, it's fine, the pivot table has the correct name and it is inserted on the correct row and column but then I get error messages on the Define Pivot Cache section. How do I fix this?

Tbl = d.Parent.Cells(d.Row, lCol)

 

[Micron]

1004 has a myriad of messages associated with it (which is just plain stupid) so it helps to be specific about the message. To me, object-required and what you just posted are probably 2 different things.

Often when you fix one error you encounter another one because they are only raised singly at run time. Did you compile your code to check it for compile errors (run time errors are not captured by compiling)? I don't have experience with what you're doing so without the workbook there's not much I can do other than to suggest that you step through the code and verify that your variable values are what you expect. You can also use the immediate window for that with the added advantage that you can also test properties such as .Value, .Name and ensure object variables are not empty e.g. ?IsEmpty(myVariable).

 

[th259]

Attached is a screenshot of the filtered pivottable on various1 worksheet, the macro already show details of all the visible items on Row Labels and named each worksheet. I need to create PivotTable on each of the new worksheets as seen on the attached screenshot.