VBA setting new filter of pivot table in another file based on cell value-CodePudding

I m a newbie to vba and need your help: I managed to create a code which opens a different excel file called "test" when making a double click in any cell of column H. So far so good.

In the file "test" there is a pivot table and I want to set the filter there with the respective value of the first file's C column where I made the double click on the H column. For example, if I make a double click on H30, I want to copy the value of C30, if I double click on H50, I want to copy the value of C50 and insert it as a new filter in the pivot table of the "test" file. Therefore I created the "test" macro. Hope that makes sense lol.

That is my current code, which always takes the C24 value and inserts it in the filter.

Many thanks guys!

Sub test()
'
' test Makro
'

'
    Range("C24").Select
    Selection.Copy
    Windows("test.xlsx").Activate
    ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Abfrage].[D_JL_NR].[D_JL_NR]").VisibleItemsList = Array( _
        "[Abfrage].[D_JL_NR].&[15109]")
    Windows("test.xlsx").Activate
End Sub

CodePudding user response：

From the comments on the questions.

You'll want to hand over the cell's value from the event to the macro you are working with. This will look something like this:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Call Test(Target.Offset(0, -5).Value)
End Sub

and the macro that's starting you'll want like this:

Sub Test(str As String)

    MsgBox str

End Sub

The value you clicked minus 5 columns will be handed over and available as "str" in the other macro

Above example will give you a message with what the selection is, but from there you can put "str" into wherever you need it

CodePudding user response：

Range(Target.Offset(0, -2).Address).Select ' Select Cell -2 rows from click