Specify conditions in one sheet to fill another sheet

[gubertu]

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 Cell = column B" where the value in "column C = Value".

3. The macro/code should find the sheet mentioned in column A "Tab name" and place the values (if there are several values in the same destination cell, they should be added) in the destination cell of that mentioned sheet.

I trust I explained myself well.
Thanks in advance!

EXAMPLE

Sheet "Data".

	A	B	C
1	Tab Name	Destination Cell	Value
2	BS	A1	1.000
3	BS	A1	500
4	BS	A2	1.000

<tbody>
</tbody>

Sheet "BS"

	A
1	1.500
2	1.000

<tbody>
</tbody>

 

[onlyadrafter]

Hello,

have assumed the destination sheets already exist in the workbook. Also assumed Column D is available in the Data Sheet.

Sub ADD_UP()
    Application.ScreenUpdating = False
    With Sheets("Data")
        For MY_ROWS = 2 To .Range("A" & Rows.Count).End(xlUp).Row
            If .Range("D" & MY_ROWS).Value <> "Y" Then
                .Range("D" & MY_ROWS).Value = "Y"
                MY_TOTAL = .Range("C" & MY_ROWS).Value
                For MY_NEXT_ROWS = MY_ROWS + 1 To .Range("A" & Rows.Count).End(xlUp).Row
                 If .Range("A" & MY_NEXT_ROWS).Value & .Range("B" & MY_NEXT_ROWS).Value = _
                    .Range("A" & MY_ROWS).Value & .Range("B" & MY_ROWS).Value Then
                        MY_TOTAL = MY_TOTAL + .Range("C" & MY_NEXT_ROWS).Value
                        .Range("D" & MY_NEXT_ROWS).Value = "Y"
                End If
                Next MY_NEXT_ROWS
            Sheets(.Range("A" & MY_ROWS).Value).Range(.Range("B" & MY_ROWS).Value).Value = MY_TOTAL
            MY_TOTAL = 0
            End If
        Next MY_ROWS
        .Columns("D").ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

 

[gubertu]

Hi and thanks for your help!

The code works perfect, but I would like to know if there´s a possibility to delete the values that are placed in sheet "BS", when I delete the values in column "C" of sheet "Data".

Because If I run the macro and then I delete the values in column C, Sheet "Data", the linked values still continue in sheet "BS".

Thanks!

 

[onlyadrafter]

Hello,

This code needs to go in to the 'Data' Sheet code window, not a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then
    Application.ScreenUpdating = False
    With Sheets("Data")
        For MY_ROWS = 2 To .Range("A" & Rows.Count).End(xlUp).Row
                If .Range("A" & MY_ROWS).Value & .Range("B" & MY_ROWS).Value = _
                    .Range("A" & Target.Row).Value & .Range("B" & Target.Row).Value Then
                        MY_TOTAL = MY_TOTAL + .Range("C" & MY_ROWS).Value
                End If
        Next MY_ROWS
        Sheets(.Range("A" & Target.Row).Value).Range(.Range("B" & Target.Row).Value).Value = MY_TOTAL
    End With
    Application.ScreenUpdating = True
    End If
End Sub

 

[gubertu]

Thanks onlyadrafter!! It works perfect!