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.

 

[TheVillageIdiot]

In the Sheet Module for the "Data" worksheet, try the following.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const kLookCol As Long = 9
Const kLookRowFirst As Long = 8
Const kLookRowLast = 220


Dim xlApp As Excel.Application
Dim wksCalc As Excel.Worksheet
Dim wksOther As Excel.Worksheet
Dim wksCert As Excel.Worksheet
Set xlApp = Excel.Application


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

With ThisWorkbook
    Set wksCalc = .Sheets("Calculation")
    Set wksOther = .Sheets("Other")
    Set wksCert = .Sheets("Certificate")
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
                        wksCalc.Cells(20, kLookCol) = .Value
                        wksOther.Cells(5, "AQ") = .Parent.Cells(.Row, 26).Value
                        wksCert.Cells(20, "P") = .Parent.Cells(.Row, 25).Value
                        .Parent.Cells(.Row, "M") = wksCalc.Cells(20, "W").Value
                        .Parent.Cells(.Row, "R") = wksCalc.Cells(20, "X").Value
                        .Parent.Cells(.Row, "T") = wksCalc.Cells(20, "Y").Value
                    End If
                End If
            End If
        End If
    End With
   
    Set wksCalc = Nothing
    Set wksOther = Nothing
    Set wksCert = Nothing
With xlApp
    .EnableEvents = True
    .ScreenUpdating = True
End With
 Set xlApp = Nothing 
   
End Sub

 

[TheVillageIdiot]

Wasn't clear to me when the calculations on the Calculation sheet occurred and when you wanted to write the results back to the Data sheet.

If the calcs are lengthy, you *may* write before the calcs are done. You may want to try the following additional inserted code in that case.

                        wksCalc.Cells(20, kLookCol) = .Value
                        wksOther.Cells(5, "AQ") = .Parent.Cells(.Row, 26).Value
                        wksCert.Cells(20, "P") = .Parent.Cells(.Row, 25).Value


                        If Not xlApp.CalculationState = xlDone Then
                            DoEvents
                        End If


                        .Parent.Cells(.Row, "M") = wksCalc.Cells(20, "W").Value
                        .Parent.Cells(.Row, "R") = wksCalc.Cells(20, "X").Value
                        .Parent.Cells(.Row, "T") = wksCalc.Cells(20, "Y").Value

 

[mamun_ges]

Thank you for the support.
I wanted to use a command button, So when the calculation was complete the button clicked the data copy from the calculation sheet to the datasheet.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Command Button to paste data from specific Cells of Specific sheet to specific Cells of Specific sheet - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mamun_ges]

Hi, Sorry for the cross-post.
Yes, the post is done by me and the link is below
Command Button to paste data from specific Cells of Specific sheet to specific Cells of Specific sheet - OzGrid Free Excel/VBA Help Forum

The code is good but creates problems for the Datasheet. Whenever I select any cell in between Column I8:I1220 it changes the current value of W,X,Y cell value picked from the calculation sheet. But the calculation sheet condition is not applicable for the current selection.

That's why I need a command button in the calculation sheet to copy data from the M,R,T cell of the Calculation sheet to the Data Sheet W,X,Y cell of the corresponding column.

Can It be done that way. Appreciate your help.

 

[mamun_ges]

Hi,

So, due to the cross-posting, my thread will remain unresponsive.

I am really sorry for that. Wish someone helps me to find a way.

Thanks in Advance.

 

[Fluff]

Now cross posted here Command Button to initiate Search and copy-paste without supplying a link.

In future please ensure that you follow the rules.

 

[mamun_ges]

Hi Moderator,

I tried to provide link on that forum but as new user I didn't get the permission to post a link there. I am really Sorry for non-complying with the forum rules. I ensure this will not ever happened.

Thanks to all expertise.

 

[TheVillageIdiot]

Still a bit unclear...

You want one routine that is event driven and triggers a set of calculations and copy/paste into a variety of cells. You then want another routine to do some additional transfers between worksheets. Is that correct?

I'm not concerned about the cross posting. That's for the moderators to handle (as they do nicely here). Can you take out from the above the parts you do not want and see if you are getting the desired outcome? Then we can go forward with the next step(s).