RE: Pivot table creation in existing sheet- VBA issues

[Paul_Hossler]

Try this, but I have to say that I don't see where you're going with this

Option Explicit


Sub createPivotTables()
    Dim rData As Range
    Dim wsPT As Worksheet, wsItem As Worksheet
    Dim myPivotTable As PivotTable
 
    For Each wsItem In ActiveWorkbook.Worksheets
        If wsItem.Name = "Main Data" Then GoTo NextSheet    '   space in from of tab name
        If wsItem.Visible <> xlSheetVisible Then GoTo NextSheet '   sheet2 is hidden
 
        Set rData = wsItem.Cells(1, 1).CurrentRegion
    
        Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=rData).CreatePivotTable(TableDestination:=wsItem.Cells(1, 1).Offset(0, rData.Columns.Count + 2))
 
        With wsItem.PivotTables(1)
            .PivotFields("OrderDate").Orientation = xlRowField
            .PivotFields("Region").Orientation = xlRowField
            .PivotFields("Rep").Orientation = xlRowField
            .PivotFields("Item").Orientation = xlRowField
            .PivotFields("Units").Orientation = xlRowField
            .PivotFields("Unit Cost").Orientation = xlRowField
                With .PivotFields("Total")
                    .Orientation = xlDataField
                    .Position = 1
                    .Function = xlSum
                    .NumberFormat = "#,##0.00"
                End With
        
            .RowAxisLayout xlTabularRow
            
            .PivotFields("OrderDate").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            .PivotFields("Region").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            .PivotFields("Rep").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            .PivotFields("Item").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            .PivotFields("Units").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            .PivotFields("Unit Cost").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            .PivotFields("Total").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
            
            .ColumnGrand = False
            .RowGrand = False
            
            .PivotFields("OrderDate").AutoSort xlAscending, "OrderDate"
            .PivotFields("Region").AutoSort xlAscending, "Region"
            .PivotFields("Rep").AutoSort xlAscending, "Rep"
        End With
    
NextSheet:
    Next
End Sub