Worksheet_change not working? 1

[tjonessi]

I created this macro by using recorder and modified it alittle bit, but the update is not working automatically when deleting a row from worksheet2 to update worksheet1. All I want it to see when #REF appear in one cell then update the remaining cells. What am I doing wrong? TIA.

Sub Worksheet_Change(ByVal Target As Range)
Sheets("Efficiency").Select
If Range("B3") = "=+'M32#1'!#REF!" Then
Range("B3").Select
ActiveCell.Formula = "=+'M32#1'!A2"
Range("C3").Select
ActiveCell.Formula = "=+'M32#1'!B2"
Range("D3").Select
ActiveCell.Formula = "=+'M32#1'!M2"
Range("E3").Select
ActiveCell.Formula = "=+'M32#1'!D2"
Range("F3").Select
ActiveCell.Formula = "=+'M32#1'!G2"
Range("G3").Select
ActiveCell.Formula = "=+'M32#1'!H2"
Range("H3").Select
ActiveCell.Formula = "=+'M32#1'!I2"
ActiveWorkbook.Save
End If
End Sub

 

[Fr33dan]

You want to try and avoid .Select as much as possible. As for why it's not working. Your if statement doesn't check what you want correctly.

Sub Worksheet_Change(ByVal Target As Range)
    If Application.WorksheetFunction.IsError(Sheets("Efficiency").Range("B3")) Then
        Sheets("Efficiency").Range("B3").Formula = "=+'M32#1'!A2"
        Sheets("Efficiency").Range("C3").Formula = "=+'M32#1'!B2"
        Sheets("Efficiency").Range("D3").Formula = "=+'M32#1'!M2"
        Sheets("Efficiency").Range("E3").Formula = "=+'M32#1'!D2"
        Sheets("Efficiency").Range("F3").Formula = "=+'M32#1'!G2"
        Sheets("Efficiency").Range("G3").Formula = "=+'M32#1'!H2"
        Sheets("Efficiency").Range("H3").Formula = "=+'M32#1'!I2"
        ActiveWorkbook.Save
    End If
End Sub

that eliminates the selects and should fix the problem

 

[tjonessi]

I was told to insert module (new) in Visual Basic Editor then put the macro in there and save it. I copied the macro and it still did not update it. Did I put it in the wrong place? This "Worksheet_change" automatically update whenever there is a changes, correct?

TIA

 

[VRoscioli]

The code should be placed not in a new module but in the module for the worksheet you wish to apply it to.


-V

 

[tjonessi]

FR33dan - I still don't understand why my if statement is not working. I copied your code and it still does not work? Is it because the if statement is still not correct?

VRoscioli - thanks, I tried a simple code and it worked. Unfortuntely, I still can't get what I need to work.

This may be alittle off subject, but where can I find the list of all those codes to use for what? It seems I am behind on this, and I have old VB script book that doesn't seems to help me out anymore.

TIA.

 

[tjonessi]

Just so everyone knows, I've learned this worked great for me.
=INDIRECT("'M32#1'!A2")

Thanks so much for everyone's help.