Running an Event Handling Macro

helpneeded04

New Member
Joined
Jun 9, 2015
Messages
9
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
Private subs don't appear in the macro list. Just remove the "Private".
 

helpneeded04

New Member
Joined
Jun 9, 2015
Messages
9
Hi Joe,

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,470
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 9, 2015
Messages
9
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,470
Office Version
365
Platform
Windows
When do you want this code to be run?
 

Watch MrExcel Video

Forum statistics

Threads
1,090,487
Messages
5,414,829
Members
403,548
Latest member
frostinheart

This Week's Hot Topics

Top