vba - 使用 VBA Excel 选择开始日期 - 结束日期

Question

我的代码有些问题。我想用 VBA 按钮选择开始日期和结束日期。

这是我的代码。有人可以帮助我吗？多谢...

(对不起，我的英语不好)。

我的代码:

Sub CARI()
Dim objname As String
Dim jumpv As Integer
Dim I As Integer
Dim S1 As Date
Dim S2 As Date

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Dashboard").Select
objname = Cells(5, 5).Value
S1 = Cells(6, 4).Value
S2 = Cells(6, 9).Value
jumpv = 4

 Worksheets("PV").Activate
For I = 4 To jumpv
    Application.StatusBar = "Loading.. ( " & Round(I / jumpv * 100, 0) & ")%"

        Sheets("PV").Select
        ActiveSheet.PivotTables("PV" & I).PivotFields("REGION").ClearAllFilters
        ActiveSheet.PivotTables("PV" & I).PivotFields("REGION").CurrentPage = objname
        ActiveSheet.PivotTables("PV" & I).PivotFields("DAY").ClearAllFilters
        ActiveSheet.PivotTables("PV" & I).PivotFields("DAY").PivotFilters.Add _
        Type:=xlDateBetween, Value1:=S1, Value2:=S2
        ActiveSheet.PivotTables("PV" & I).PivotFields("DAY").AutoSort _
        xlAscending, "DAY"
Next I
Sheets("Dashboard").Select
Application.StatusBar = ""
MsgBox "Done!"
End Sub

Answer 1

试试下面的代码，代码内的解释为注释:

Option Explicit

Sub CARI()

Dim objname As String
Dim jumpv As Integer
Dim I As Integer
Dim S1 As Date
Dim S2 As Date

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Sheets("Dashboard") ' <-- use With instead of Activate or Select the sheet
    objname = .Cells(5, 5).Value
    S1 = .Cells(6, 4).Value
    S2 = .Cells(6, 9).Value
End With

jumpv = 4

With Worksheets("PV")
    For I = 4 To jumpv
        Application.StatusBar = "Loading.. ( " & Round(I / jumpv * 100, 0) & ")%"

        .PivotTables("PV" & I).PivotFields("REGION").ClearAllFilters
        .PivotTables("PV" & I).PivotFields("REGION").CurrentPage = objname
        .PivotTables("PV" & I).PivotFields("DAY").ClearAllFilters

        ' when filtering dates, safest way is to covert to Double (their actual value, not their format)
        .PivotTables("PV" & I).PivotFields("DAY").PivotFilters.Add _
                Type:=xlDateBetween, Value1:=CDbl(S1), Value2:=CDbl(S2)
        .PivotTables("PV" & I).PivotFields("DAY").AutoSort xlAscending, "DAY"
    Next I
End With

Sheets("Dashboard").Select

Application.StatusBar = ""
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Done!"

End Sub