Pivot table variable problem

Hello

Is it possible to make the date part variable in the vba code below? I would like your help.

Code:

---

ActiveSheet.PivotTables("PivotTable1").PivotFields("Yaratma tarihi"). _
PivotFilters.Add2 Türü:=xlBefore, Değer1:="20.01.2025"

---

sample file

https://drive.google.com/file/d/13VItbEzEhqrXsnRkwhjk92WF0IuyR7nH/view?usp=sharing

Welcome vbax van_65166. 2 quick points for you to consider. When posting sections of code, please wrap them with code tags by either highlighting the code and then clicking on the hash (#) icon, or click the hash icon and then insert the code between the tags. Secondly, we would appreciate it if you used the file attachment function by clicking on the Go Advanced link bottom right of your intending post, then Manage Attachments and follow the prompts from there.

Quote:

---

Originally Posted by aussiebear

vbax van_65166'ya hoş geldiniz. Dikkate almanız gereken 2 hızlı nokta. Kod bölümlerini gönderirken, lütfen kodu vurgulayıp ardından hash (#) simgesine tıklayarak veya hash simgesine tıklayıp ardından kodu etiketler arasına ekleyerek bunları kod etiketleriyle sarın. İkinci olarak, amaçladığınız gönderinin sağ alt köşesindeki gelişmiş'e git bağlantısına tıklayarak dosya eki işlevini kullanmanızdan ve ardından ekleri yönet'e tıklayıp oradan gelen istemleri takip etmenizden memnuniyet duyarız.

---

hello, don't hold it against me, i will be more careful, good luckAttachment 31862

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

Code:

---

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

---

FYI cross-posted at least here:
https://www.excelforum.com/excel-pro...e-problem.html
https://chandoo.org/forum/threads/pi...problem.58205/