CODEWITHSUNDEEP
vbaexcel
user5103985
user5103985

Reputation: 11

Automate a macro based on a change within a range from another sheet

I am trying to automate a macro to run on sheet2 whenever a cell within a range on sheet1 is changed. I have tried a bunch of things and I don't have the vba experience to know what is wrong with them. Basically, sheet1 has my input, and I assigned a level of priority 1-5 to each item. Sheet2 shows only those items ranked 1, 3, or 4. I did this with if statements, but this leaves a bunch of blank rows in my table, so I can sort the blank rows out using the filter function. If I change a ranking on sheet1, I want my sheet2 table to automatically update. I wrote a sort function which resorts my sheet2 data appropriately but I am struggling to automate it so that it updates automatically when anything from sheet1 is changed. So far I have been using worksheet_change and can get sheet1 to refilter when sheet1 is changed, which is not what I want. Any ideas?

This is my current sort function:

Sub ReSort() 
With Worksheets("Sheet2")
    .Range("$A$2:$D$34").AutoFilter Field:=2 
    .Range("$A$2:$D$34").AutoFilter Field:=2, Criteria1:="<>" 
End With 
End Sub

Upvotes: 0

Views: 145

Answers (3)

user5103985
user5103985

Reputation: 11

I finally got it to work! For those reading this and having a similar problem, I have this code saved in sheet1:

Sub ReSort() 
'This function filters my table spanning A2:D34 by the second column and sorts out the blanks

With Worksheets("Sheet2")
    .Range("$A$2:$D$34").AutoFilter Field:=2
    .Range("$A$2:$D$34").AutoFilter Field:=2, Criteria1:="<>"
End With 
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 
'This function runs my ReSort function if any cell on sheet1 in E3:E34 or G3:G34 is changed
    If Not Intersect(Target, Range("$E$3:$E$34,$G$3:$G$34")) Is Nothing Then
        ReSort
End If
End Sub

Thanks to everyone for their help! I was seriously pulling my hair out in frustration with this.

Upvotes: 1

KFichter
KFichter

Reputation: 783

This:

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Target, Range("A:A")) Is Nothing Then 
        ' Do something
    End If 
End Sub

Should do the trick

Upvotes: 3

Darren H
Darren H

Reputation: 902

Sounds like you're on the right path, worksheet_change is the correct way to go with this as you do want the macro to run when sheet1 is changed so you need to detect that.

I suspect you're just missing one thing, the macro that runs on sheet2, put it in a module reference sheet2 explicitly

For example,

Worksheets("Sheet1").Range("A1")

instead of just

Range("A1")

Then you can call the function to run from any sheet just by using the function name

If you need more detail, post all of the code you have so far and I will happily modify it to suit

Upvotes: 0

Related Questions