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

Question

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

"I wrote a sort of function" - What function? How does it run now? — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Jul 10, 2015 at 18:46

KFichter · Accepted Answer · 2015-07-10 18:33:50Z

3

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

answered Jul 10, 2015 at 18:33

KFichter

7934 silver badges15 bronze badges

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

2 Comments

jny Over a year ago

Welcome to StackOverflow! Can you explain why it will work?

KFichter Over a year ago

Thanks! The code centers around the Intersect method, which returns an object that represents the intersection of two ranges. In this case, it returns some object that represents the intersection of the Target (cell changing) and the specified range. If the object created b the Intersect method "Is Nothing" (AKA the two do not intersect) then the code won't do anything. However, if it "Is Not Nothing" (heh), it will trigger ' Do something. This code originally domes from this 2003 post.

user5103985 · Accepted Answer · 2015-07-10 19:15:04Z

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.

Darren H · Accepted Answer · 2015-07-10 18:31:04Z

0

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

answered Jul 10, 2015 at 18:31

Darren H

9018 silver badges25 bronze badges

1 Comment

user5103985 Over a year ago

Where should I be saving my functions? Do they need to be saved in the same place?

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related