Running an Event Handling Macro

[helpneeded04]

Hello,
I have a query pulling in data from an external database. In the same worksheet, I have cells equaling cells in the query table. When data in the query changes, the equals formulas #ref out. I am trying to create a macro that will copy the formulas down whenever the query table is refreshed. I have the following in a worksheet object. However, when I try to run the macro, it does not show up in the macro list. Any help would be much appreciated.

Private Sub Worksheet_QueryTableRefresh(ByVal Target As QueryTable)
Range("F2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("F2").Select
End Sub

 

[JoeMo]

Private subs don't appear in the macro list. Just remove the "Private".

 

[helpneeded04]

Hi Joe,

Thank you for your response. Unfortunately, it still did not show up after removing the "Private".

 

[JoeMo]

Hi Joe,

Thank you for your response. Unfortunately, it still did not show up after removing the "Private".

Is it installed as sheet code or as a standard module?

 

[helpneeded04]

Is it installed as sheet code or as a standard module?

It is in the worksheet module.

 

[Norie]

That code will be triggered when the query is refreshed.

It won't appear on the macro list because it's passed an argument, Target, which is the query table that's been refreshed.

If you want to run the code manually put it in a sub in a standard module and remove the argument.

 

[helpneeded04]

That code will be triggered when the query is refreshed.

It won't appear on the macro list because it's passed an argument, Target, which is the query table that's been refreshed.

If you want to run the code manually put it in a sub in a standard module and remove the argument.

Hi Norie,

This code was not triggered when the query is refreshed.

Thank you

 

[JoeMo]

Hi Norie,

This code was not triggered when the query is refreshed.

Thank you

Where did you get the code you posted in your initial post? I'm not familiar with a worksheet event called:

Worksheet_QueryTableRefresh

 

[Norie]

When do you want this code to be run?