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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

TheVillageIdiot

Board Regular
Joined
Dec 9, 2011
Messages
63
In the Sheet Module for the "Data" worksheet, try the following.

VBA Code:
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

Board Regular
Joined
Dec 9, 2011
Messages
63
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.

VBA Code:
                        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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Board Regular
Joined
Jul 21, 2016
Messages
52

ADVERTISEMENT

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.
 

mamun_ges

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

Board Regular
Joined
Dec 9, 2011
Messages
63
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).
 

Forum statistics

Threads
1,147,733
Messages
5,742,859
Members
423,760
Latest member
photogfrog

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
Top