none
How to copy and paste values from other sheet dyminacally in excel VBA RRS feed

  • Question

  • Hello,

    I'm just starting using VBA for my tasks in excel.

    I want to copy values from previous sheet and paste into active cell of active sheet.

    there are values in "sheet1", value 1 in A1, value 2 in A2, value 3 in A3 cell.....

    There are values in sheet2, 2 in B4, 4 in B9....

    Now I want to copy values as below,

    - I will select cell A1 and click "Copy" button => value from A1 of sheet1 copyed into sellected cell(A1) of sheet2,

    - I will select cell A4 and click "Copy" button => value from A2 of sheet1 copyed into sellected cell(A4) of sheet2,

    *** value in B4 meas rows quantity to offset (copy value from selected row number - value in B4) (example, A4 - 2 = A2)

    - I will select cell A9 and click "Copy" button => value from A3 0f sheet1 copyed into sellected cell(A9) of sheet2,

    *** value in B4 meas rows quantity to offset (copy value from selected row number - value in B9) (example, A9 - 6 = A3)

    How should I make a VBA function for "Copy" button to work as above??? Please help me~~

    Thanks in advance for your advise!!!

    Thursday, February 15, 2018 1:40 AM

All replies

  • Hi JS.Son,

    I guess it would be not hard to achieve, if I can understand correctly  what you want to do.

    I'm afraid you need to describe some criteria clearly (esp. cell B4, B9 in sheet2) or provide more details.
    What is the relationship/differnce between B4 and B9?
    e.g. What shall I do, if (value of B4 < 4)? 
    Your wrote 4 in B9. What does the following mean? 
    *** value in B4 meas rows quantity to offset
      (copy value from selected row number - value in B9) (example, A9 - 6 = A3)


    Regards,

    Ashidacchi

    P.S.
    This is a sample only by my guess....

    • Edited by Ashidacchi Thursday, February 15, 2018 6:22 AM
    Thursday, February 15, 2018 5:12 AM
  • Normally we would get the value from a cell and then put the value in another cell. Normally there would be no need to use the clipboard for this. Is there something you did not explain that requires the use of the clipboard?

    Are both worksheets in the same workbook?



    Sam Hobbs
    SimpleSamples.Info

    Thursday, February 15, 2018 5:22 AM
  • Hello JS.Son,

    >>There are values in sheet2, 2 in B4, 4 in B9

    >>value in B4 meas rows quantity to offset (copy value from selected row number - value in B9) (example, A9 - 6 = A3)

    I have never seen 6 in your previous description, according to your description, I think the value in B9 should be 6, right?

    It seems that you need use current cells's row index to minus value in next column(B column) and then use the result value as row index than get the value form column A in worksheet1.

    If so, I would suggest you check below code and demonstration as reference.

    If not, please detail your issue more clearly. Thanks for understanding.

    Private Sub CommandButton1_Click()
    Dim objCell As Range
    Set objCell = ActiveCell
    If Not Intersect(objCell, Worksheets("Sheet2").Columns(1)) Is Nothing Then
    RowIndex = objCell.Row - objCell.Offset(0, 1)
    objCell.Value = Worksheets("Sheet1").Cells(RowIndex, 1)
    End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 21, 2018 5:39 AM