Continuous VBA commands

Question

I am having an issue making a continuous macro work. I have a filter set on a 'dashboard' sheet, which pulls information from a different sheets. I would like the macro to auto run, continuously updating the filter when ever the cells in the source sheet update. My code is below: what needs amending? thanks so much. (S&P500 is source sheet. Dashboard is sheet with the Macro on)

    Sub Yes ()
    '
    ' Yes Macro
    '
    'Private Sub S&P500Stocks_Activate()
        ActiveSheet.Range("$A$1:$E$499").AutoFilter Field:=1, Criterial:="Yes", _
             Operator:=xlAnd
    'Private Sub S&P500Stocks_Change(ByVal Target As Range)
        ActiveSheet.Range("$A$1:$E$499").AutoFilter Field:=1, Criterial:="Yes", _
             Operator:=xlAnd
    End Sub

Help would be awesome

Gary's Student · Accepted Answer · 2015-05-23 22:29:59Z

1

First make sure Sub Yes() is in a Standard Module rather than on a worksheet.
Second change ActiveSheet to Sheets("S&P500")
Third make Sub Yes() Public

Then to go the worksheet code area for Sheets("S&P500") and install this event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
      Call Yes
   Application.EnableEvents = True
End Sub

answered May 23, 2015 at 22:29

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sniper 81 Over a year ago

I am however a bit of a novice.

Sniper 81 Over a year ago

How do I make sure something is a standard module rather than a worksheet? How do I make Yes public? thanks so much!!

Sniper 81 Over a year ago

I have got all the above auto working now. If I have set another sheet up with a macro that I want to run continuously using the same worksheets but different info within them. If I use the code: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Call Yes Application.EnableEvents = True End Sub

Sniper 81 Over a year ago

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Call Yes Application.EnableEvents = True Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Call Dboard 2 Application.EnableEvents = True End Sub

Sniper 81 Over a year ago

For some reason it is showing an error....When trying to have two commands like this in the worksheet event macro....

Collectives™ on Stack Overflow

Continuous VBA commands

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related