[SOLVED] Running Multiple Macros on Different Cell Changes

  • Thanks in advance for your patience with me as I'm new to these forums, and pretty new to Macros/VBA in Excel.


    I have a workbook with multiple sheets that all feed data into two main sheets, called 'Timeline' and 'Dashboard'


    I have a macro to run an AutoResize function on 'Dashboard' rows:



    And then I have the page code of each data source sheet execute the AutoResize macro when any of three different cells are modified on the sheets. From the page code:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
         
        Select Case Target.Address
        Case "$F$6", "$F$13", "$F$14": AutoResize
        Case Else: Exit Sub
        End Select
        
    End Sub


    So my question is this: I have the following NEW macro called AutoFilter, created to run an auto filter to hide any rows where values of "#N/A" are present from the 'Timeline' sheet:


    Code
    Sub AutoFilter()
           Range("A1").AutoFilter Field:=2, Criteria1:="<>#N/A"
    End Sub


    When I run it manually, it filters out rows with "#N/A" just fine. So I want to then make an addition to the page code above to execute AutoFilter if a range of cells are changed. I tried the following (copied the WHOLE sheet code) but it doesn't work when I modify the cell in the column identified:



    With this, the first portion still works (I modified one of the cells, and it executed the AutoResize macro just fine) - but if I enter data into the column that should trigger the AutoFilter macro, it doesn't fire off. I go to the 'Timeline' sheet, and see the new row isn't visible. But when I open up the Macros and run AutoFilter manually, it pops up just fine.


    I have tried just using Case "$C$18" in case ranges were not possible with this code, but that didn't help. I also tried doing just the Worksheet_Change1 code in a blank sheet using A1 as the reference trigger for firing off the AutoFilter macro, but that did not work either.


    So that's my issue - what am I missing to make my life a whole lot easier on this end? Thanks in advance for any help, and please do let me know if I'm missing any information needed to assist me.


    (Per the rules, I've also asked this question at ExcelForum.)

  • Re: Running Multiple Macros on Different Cell Changes


    Hi SF007,


    will this work?


    There is only one Worksheet_Change event which is triggered when a change occurs. You already captured the event that you want for AutoResize and for AutoFilter so there's no reason to have another event sub. You have the right structure already set up with a Case structure so you just need to add another Case which triggers the AutoFilter event. You were so close already!


    Also, there is no such thing as Worksheet_Change1() event.


    cheers!

  • Re: Running Multiple Macros on Different Cell Changes


    Ahh, thanks for the education, turbonate! I think we're heading in the right direction, because now instead of nothing happening at all, I'm getting a compile error: "Invalid Use of Property" and then it highlights my "AutoFilter" macro name in the VBA window.


    This is the code I have for the auto-filter:


    Code
    Sub AutoFilter()
           Range("A1").AutoFilter Field:=2, Criteria1:="<>#N/A"
    End Sub


    Does anything pop out that makes the problem obvious?

  • Re: Running Multiple Macros on Different Cell Changes


    Your range reference consist of a single cell/column . . . A1, and you have specified that the filter is to be applied to "Field 2" (not encompassed by the range reference)


    I don't see the exact range you want, but you could try this.


    Code
    Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="<>#N/A"
  • Re: Running Multiple Macros on Different Cell Changes


    you could also pass the target range to the sub:



    You might try AAE's suggestion as I didn't look up the AutoFilter function, I'm just suggesting another way to reference the cell you're working with. It might make more sense to pass the Target to the AutoResize() sub than the AutoFilter() sub. What you have will work, it's just a matter of if one of these ideas will work better.

  • Re: Running Multiple Macros on Different Cell Changes


    Thanks so much for the awesome help so far, guys. I think we're close!


    Ok, so I first tried what AAE said, but that did not seem to work for the AutoFilter macro. The same error message would come up when I edited a field on the other worksheet that was supposed to run the AutoFilter macro. I renamed my AutoFilter macro to AutoFilterMacro, thinking perhaps the issue was that I was calling a macro something that is already a property within Excel. I reverted my code on the worksheet to the original suggestion from turbonate, and when I entered text in C26, which should trigger the macro on the Timeline sheet, I did not get the error message, but the AutoFilterMacro function had not been run when I went to check the Timeline sheet. I was, however, able to manually execute the AutoFilterMacro macro, and it unfiltered the new row as it should have.


    So I'm wondering - is the issue that there's nothing pointing to the specific worksheet that the AutoFilterMacro macro should be run on? Keep in mind, I'll be needing to replicate this functionality on 16 other worksheets, all which should run the AutoFilterMacro Macro on the Timeline worksheet. I just did a bit more digging, and found that I could insert a sheet reference in front of the Range identifier on my AutoFilterMacro macro. In doing this, I can run that macro on any sheet and still have it filter on the 'Timeline' (or 'Sheet2') sheet! For reference, here is the AutoFilterMacro code, followed by the Page code on the first of the 17 worksheets:



    Code
    Sub AutoFilterMacro()
           Sheet2.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="<>#N/A"
    End Sub


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
         
        Select Case Target.Address
        Case "$F$6", "$F$13", "$F$14": AutoResize
        Case "C18:C56": AutoFilterMacro
        Case Else: Exit Sub
        End Select
        
    End Sub


    I can almost see the light at the end of the tunnel! Again, if you need more information please don't hesitate to let me know!

  • Re: Running Multiple Macros on Different Cell Changes


    It is probably best to upload a sampel workbook.


  • Re: Running Multiple Macros on Different Cell Changes


    Ah, just got what appears to be a good snippet of code from excelforum.com - posting here to close the loop!


    AAE, thank you (and turbonate!) for getting me thus far. I had to come across the cases in a different way in the page code to get things to fire off for the AutoFilterMacro macro. The resultant page code to be placed on the 17 source worksheets, that will still roll up to the Dashboard and Timeline pages, is as follows:



    Thanks again for all of your help - I'm sure to be sticking around here. You guys are so helpful, and I want to see if I can positively impact those around here as well with my (much more limited than others') knowledge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!