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

mamun_ges

Board Regular
Joined
Jul 21, 2016
Messages
52
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.
VBA 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.
 
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.
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
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.

VBA Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top