Pivot table variable problem

[van_65166]

Hello

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


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

[Aussiebear]

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.

[van_65166]

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 luck31862

[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

[Aflatoon]

FYI cross-posted at least here:
https://www.excelforum.com/excel-programming-vba-macros/1432530-pivot-table-variable-problem.html
https://chandoo.org/forum/threads/pivot-table-variable-problem.58205/