Command Button to paste data to specific Cell of Specific Sheet with matched cell row

[mamun_ges]

Hi, I have searched in many forums but don't get the best result the suit to my problem.

I have two worksheets of a workbook.
One is the "Data" and the other is a "Calculation".
First Condition: When I select any cell in between Column I8:I1220 of the Data Sheet, the cell value is immediately copied to the Calculation Sheet to the "I20" cell.
The is some calculation doing. After the calculation is done.
Second Condition: I want a command button that picks values of M20, R20, and T20 from "calculation" to the "Data" Sheet of the same row where the first data select. The data will be paste to W, X & Y cells of the selected cell row of the Data Sheet.

The First condition is to copy from Data file to Calculation, I use the below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
 If Target.Count > 1 Then Exit Sub
 If Target.Column = 9 Then
 If Target = vbNullString Then
 Exit Sub
 Else
 Sheets("Calculation").[I20] = Target
 
[COLOR=rgb(250, 197, 28)] With Sheets("Other")
 .[aq5] = Cells(Target.Row, 26)[/COLOR]

 End With
 
 End If
 End If
 
End Sub

Second Condition: I need a command button code for the second condition to pick cell values of M20, R20, and T20 from "calculation" to Data of sheet select row and column of W, X & Y. If those cells have data already it will be updated and a message will show "data update".

Third Condition: When I select any cell in between Column I8:I1220 of the Data Sheet, (as you see Yellow Text) 26 no column data paste to "other" sheet in aq5 cell.
Here I also want to copy& paste 25 no columns to the "certificate" Sheet p20 cell. (If I write above like Yellow Text) it returns error.

I hope someone helps me and would be greatly appreciated.

 

[mamun_ges]

Thanks for your response.

"Need command Button because It needs some time to calculate the value in the Calculation Sheet. After that, transpose to Data Sheet can be done"

"Now refine the condition ignore the First condition "You want one routine that is event-driven and triggers a set of calculations and copy/paste into a variety of cells."

Two worksheets, Data, and Calculation. Four cells in worksheet "Calculation" (Cells are I20, M20, R20, T20) and Four-column in worksheet "Data" ( I, W, X, Y) which row starts from 9 -1220.

If the command button is clicked.

Cell I20 value of "Calculation" search in between Cells I9:I1220 of Data Worksheet. If match found then M20, R20, and T20 Value of Calculation Sheet paste in the corresponding matched row of W, X, Y of Data Sheet and a popup message show that "value updated".

Thanks Again.

 

[mamun_ges]

Thanks for your response.

"Now ignore the First condition "You want one routine that is event-driven and triggers a set of calculations and copy/paste into a variety of cells."

"Need command Button because It needs some time to calculate the value in the Calculation Sheet. After that, Data transpose to Data Sheet can be done"


Two worksheets, Data, and Calculation. Four cells in worksheet "Calculation" (Cells are I20, M20, R20, T20) and Four-column in worksheet "Data" ( I, W, X, Y) which row starts from 9 -1220.

If the command button is clicked.

Cell I20 value of "Calculation" search in between Cells I9:I1220 of Data Worksheet. If match found then M20, R20, and T20 Value of Calculation Sheet paste in W, X, Y of the matched cell row of Data Sheet and a popup message show that "value updated".

Hope I may clear my thoughts to you,

Thanks Again.

 

[mamun_ges]

 

[mamun_ges]

Dear TheVillageIdiot

I have changed your provided code for the expected result.
But seems something wrong I have done. Will you check the code, please.

When I click the command button it copies M20, R20, T20 to Datasheet in W X Y but not in the matched row.
Here is the Test File link.

Sub Button2_Click()

Const kLookCol As Long = 9
Const kLookRowFirst As Long = 8
Const kLookRowLast = 1220


Dim xlApp As Excel.Application
Dim wksCalc As Excel.Worksheet
Dim wksData As Excel.Worksheet
Set xlApp = Excel.Application


With xlApp
    .ScreenUpdating = False
    .EnableEvents = False
End With

With ThisWorkbook
    Set wksCalc = .Sheets("Calculation")
    Set wksData = .Sheets("Data")
    Set Target = wksCalc.Cells(20, "I")

End With

With Target

         If .Cells.Count = 1 Then
            If .Row >= kLookRowFirst And .Row <= kLookRowLast Then
                If .Column = kLookCol Then
                    If Len(.Value) <> 0 Then
                        wksData.Cells(.Row, "W") = wksCalc.Cells(20, "M").Value
                        wksData.Cells(.Row, "X") = wksCalc.Cells(20, "R").Value
                        wksData.Cells(.Row, "Y") = wksCalc.Cells(20, "T").Value
                    End If
                End If
            End If
        End If
    End With
 
    Set wksCalc = Nothing
    Set wksData = Nothing
With xlApp
    .EnableEvents = True
    .ScreenUpdating = True
End With
 Set xlApp = Nothing
 
End Sub

Thanks in advance.