Pivot table variable problem

[Paul_Hossler]

Not holding anything against anybody

I did clean (OK, change) some macro a little to try and see what's going on and for my testing

I think that the DateBefore has to be in US format, i.e. mm/dd/yyyy, regardless of regional setting. So give this a shot. You might need to experiment a little

There were some things in the marco that I didn't see the reason for, but I tried to leave them alone

Option Explicit

Sub ula_sevk()
    ' PURPOSE: Delete all Pivot Tables in your Workbook
    ' SOURCE: www.TheSpreadsheetGuru.com
    Dim sht As Worksheet, shtData As Worksheet, shtPT As Worksheet
    Dim pvt As PivotTable
    Dim dateBefore As Date
    ' Loop Through Each Pivot Table In Currently Viewed Workbook
    For Each sht In ActiveWorkbook.Worksheets
        For Each pvt In sht.PivotTables
            pvt.TableRange2.Clear
        Next pvt
    Next sht  
    Set shtData = Worksheets("Sayfa1")
    With shtData
        .Select
        .Range("H1").FormulaR1C1 = "GÖNDEREN"
        .Range("J1").FormulaR1C1 = "TESLÝM ALAN"
        .Range("H2").Select
    End With    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("PT").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0    
    Worksheets.Add
    Set shtPT = ActiveSheet
    shtPT.Name = "PT"    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=shtData.Name & "!" & shtData.Cells(1, 1).CurrentRegion.Address, _
    Version:=7).CreatePivotTable TableDestination:=shtPT.Name & "!R3C1", TableName:="PivotTable1", DefaultVersion:=7    
    With ActiveSheet.PivotTables("PivotTable1")
        With .PivotCache
            .RefreshOnFileOpen = False
            .MissingItemsLimit = xlMissingItemsDefault
        End With        
        .ColumnGrand = False
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = False
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow    
        .RepeatAllLabels xlRepeatLabels    
        .RowAxisLayout xlTabularRow    
        With .PivotFields("Malý teslim alan")
            .Orientation = xlRowField
            .Position = 1
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        End With        
        With .PivotFields("TESLÝM ALAN")
            .Orientation = xlRowField
            .Position = 2
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        End With   
        With .PivotFields("Teslimat")
            .Orientation = xlRowField
            .Position = 3
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        End With    
        With .PivotFields("Yaratma tarihi")
            .Orientation = xlRowField
            .Position = 4
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        End With    
        With .PivotFields("GÖNDEREN")
            .Orientation = xlRowField
            .Position = 5
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        End With        
        .PivotFields("Yaratma tarihi").AutoGroup    
        dateBefore = DateAdd("d", -7, Date)
        .PivotFields("Yaratma tarihi").PivotFilters.Add2 Type:=xlBefore, Value1:=Format(dateBefore, "mm/dd/yyyy")
    End With    
    With shtPT
        .Range("D4").Ungroup
        .Cells.EntireColumn.AutoFit
        .Range("D3").Select
    End With
End Sub