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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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".
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
76,278
Office Version
  1. 365
Platform
  1. Windows
When do you want this code to be run?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,269
Messages
5,635,190
Members
416,846
Latest member
ImGoing2needaFormula

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