Simulate "F2" and "Enter" with a macro triggered on a Slicer in a different work sheet

samrat_castle

New Member
Joined
Sep 20, 2015
Messages
7
Hi Guys,

I am trying to simulate "F2" and "Enter" SendKeys event, on a specific cell "C1" in a specific Sheet "Past Login", which has 2 Pivots from a data source in a different workbook.

I have a Slicer pulling data from another data source in the same workbook. This slicer has dates and i need the change in date to trigger change in the pivots on "Past Login" sheets.

I tried connecting the slicer with those pivots but as they are from different data source, so no go.

I have now tried a different approach and have a code to change pivot filters on basis of a cell value change to a specific date, and that cell is pulling reference from another cell which in turn is changed on basis of slicer option selected.

This is the code I am using to change pivot filters:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pt As PivotTable
    Const strField As String = "Date"
    
    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Target.Address = "$C$1" Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    .ClearAllFilters
                    .CurrentPage = Target.Value
                End With
            Next pt
        Next ws
    End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Now, to trigger this change, I need to press F2 and Enter on that cell every time to change the pivot filters. I need this to happen automatically when I select a specific date on the Slicer.

I tried below code to do it. but it only works when tried from the same sheet.

Code:
Sub AssignedDate3_Click1()
    Dim c As Range
        For Each c In Worksheets("Past Login").Range("C1").Cells
        c.Select
        SendKeys "{F2}", True
        SendKeys "{ENTER}", True
    Next
End Sub

While trying to do it from Slicer, its gives the error:

Run-time error '1004':
Select method of Range class failed

Please help with this.
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,136,258
Messages
5,674,670
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top