Can I transform a Worksheet_Change event in a normal macro?

[gubertu]

Hi all,

I have the following code. This is a Worksheet_Change event and I would like to know if it is possible to modify this code so I can execute it when I want, as a normal macro, pressing F5.

Thanks in advance!

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("BV1:BV" & Range("CJ" & Rows.Count).End(xlUp).Row)) Is Nothing Then
    Application.ScreenUpdating = False
    With Sheets("SYS Agregado")
        For MY_ROWS = 2 To .Range("CJ" & Rows.Count).End(xlUp).Row
                If .Range("CJ" & MY_ROWS).Value & .Range("CL" & MY_ROWS).Value = _
                    .Range("CJ" & Target.Row).Value & .Range("CL" & Target.Row).Value Then
                        MY_TOTAL = MY_TOTAL + .Range("BV" & MY_ROWS).Value
                End If
        Next MY_ROWS
        Sheets(.Range("CJ" & Target.Row).Value).Range(.Range("CL" & Target.Row).Value).Value = MY_TOTAL
    End With
    Application.ScreenUpdating = True
    End If
End Sub

 

[Joe4]

Can you post some sample data, and explain exactly what you want this to do?

 

[gubertu]

Hi,

I received that code in one question I did years ago. The link is the following.

Hope you could help me.

Thanks!

Specify conditions in one sheet to fill another sheet

Hi all, I am looking for a VBA code/macro for doing the following (see below example). 1. In sheet "Data", column "A = Tab name" you need to specify the name of the sheet (in this case, in the workbook there is a sheet called "BS". 2. Also in sheet "Data", you need to specify the "Destination...

www.mrexcel.com

 

[Joe4]

See if this does what you want:

Sub MyMacro()

    Dim MY_ROWS As Long
    Dim MY_TOTAL As Double
    Dim sht As String
    Dim rng As String

    With Sheets("Data")
        For MY_ROWS = 2 To .Range("A" & Rows.Count).End(xlUp).Row
'           Build sheet name and range variables
            sht = .Range("A" & MY_ROWS)
            rng = .Range("B" & MY_ROWS)
'           Find value from other sheet and add it to current row
            MY_TOTAL = Sheets(sht).Range(rng).Value + .Range("C" & MY_ROWS).Value
'           Put new value on other sheet
            Sheets(sht).Range(rng).Value = MY_TOTAL
        Next MY_ROWS
    End With
    
    MsgBox "Macro complete!"
    
End Sub

Note that you will need to assign it to a keyboard shortcut or button. I don't know that you can assign it to F5, as that is the refresh button.

 

[gubertu]

Thanks a lot!

It is possible to modify the code so it works even if the column B "Destination cell" has empty cells, as in the example below?

Thanks in advance!


Sheet "Data".

	A	B	C
1	Tab Name	Destination Cell	Value
2	BS	A1	1.000
3	BS		500
4	BS	A2	1.000
5	PL	A1	5.000
6	PL		6.000

 

[Joe4]

It is possible to modify the code so it works even if the column B "Destination cell" has empty cells, as in the example below?

What should it do in that case?
Just ignore the entry?

 

[gubertu]

What should it do in that case?
Just ignore the entry?

Yes just add up the values that has the column Destination cell filled.

Thanks

 

[Joe4]

Just add an "IF" statement in there to check the value in column B, i.e.

Sub MyMacro()

    Dim MY_ROWS As Long
    Dim MY_TOTAL As Double
    Dim sht As String
    Dim rng As String

    With Sheets("Data")
        For MY_ROWS = 2 To .Range("A" & Rows.Count).End(xlUp).Row
'           Check to see if there is a value in column B
            If .Range("B" & MY_ROWS) <> "" Then
'               Build sheet name and range variables
                sht = .Range("A" & MY_ROWS)
                rng = .Range("B" & MY_ROWS)
'               Find value from other sheet and add it to current row
                MY_TOTAL = Sheets(sht).Range(rng).Value + .Range("C" & MY_ROWS).Value
'               Put new value on other sheet
                Sheets(sht).Range(rng).Value = MY_TOTAL
            End If
        Next MY_ROWS
    End With
    
    MsgBox "Macro complete!"
    
End Sub

 

[GWteB]

I don't know that you can assign it to F5, as that is the refresh button

 

[gubertu]

That´s great! This is just what I needed. Thank you very much!