Copying 2 different cell values into formula using VBA

[Julliana]

Hello team,

I'm a newbie at macros, and cannot seem to get what I need...

I have sheet 1 with a huge list of client data: accounts (Column D), product names (Column C), amounts (Column E).

What I am aiming is to create a macro that would copy data from cells located in Columns C, D and E (Sheet 1) to specific cell locations in the Sheet2.

Here's the exact scheme below. The values of cells / cells have to be copied accordingly to the formulas on the picture on the right.

I've been trying to work on this for a a while and cannot seem to be a able to create this, especially line 8 on the left screenshot.

https://ibb.co/ee0Vdo

Could you please kindly assist me in guiding through this?

Thank you!

 

[tonyyy]

Julliana,

Welcome to the Board.

You might consider the following...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim rng As Range
Set rng = Range("D2", Cells(Rows.Count, "D").End(xlUp)) 'Assume headers in Row 1
If Not Intersect(Target, rng) Is Nothing Then
    With Sheets("Sheet2")
        .Cells(4, 1).Value = Target.Value
        .Cells(8, 1).Value = Target.Offset(0, -1)
        .Cells(8, 2).Value = Target.Offset(0, 1)
        .Cells(12, 1).Value = "  T " & Target.Value & "FBUL"
        .Cells(13, 1).Value = "  T " & Target.Value & "FBUL"
    End With
    Sheets("Sheet2").Activate
End If
End Sub

The code should be pasted into the Sheet1 module.

Selecting an account in Column D on Sheet 1 will populate the relevant data into Sheet 2 at the specified locations.

Cheers,

tonyyy

 

[Julliana]

Hi Tony,

Thank you very much! A few noob questions remain though...

Should I create a new macro, and edit it by copying the code that You wrote, or should I do it the other way? No additional changes needed to the code?

Thank you in advance!

 

[Julliana]

I get an error in the link attached...

https://ibb.co/co7utz

 

[tonyyy]

Should I create a new macro, and edit it by copying the code that You wrote, or should I do it the other way? No additional changes needed to the code?

No, you do not need to create a new macro, nor do you need to edit the code. You might want to view the very short tutorial on how to Copy VBA Code to a Worksheet Module. As stated in Post #2 , the code should be pasted into the Sheet 1 module.

You do not need to "run" the code. The code will be triggered and run automatically when you select a cell in Column D of Sheet 1.

 

[Julliana]

I follow each and every step of the video, however at 2:28 where the macro should be selected, I don't see any macro to choose from...

 

[Julliana]

I have data in Sheet 1, accounts starting from cell D2, and in Sheet 2 data ranging from A1-A13.
I see no macro selection despite copying everything like the video describes...

 

[tonyyy]

I see no macro selection...

Again...

You do not need to "run" the code. The code will be triggered and run automatically when you select a cell in Column D of Sheet 1.

What happens when you select different cells/accounts in Column D of Sheet1?