none
How to copy data from column from one sheet to another? RRS feed

  • Question

  • Should I use vba macro to do it or vLookup?

    I want ID , Issue ID, Severity and Create Date from Ageing Defect Sheet to be automated from JIRA List, overwriting the exisiting data from Ageing Defect Sheet.

    Jira List Sheet from B11 onwards, C11, D11, L11 to be automated to Ageing Defect Sheet

    Also how do I populate overdue dates by a click of a button? Thankss

    Below codes are not working

    Dim lastrow As Long, erow As Long
    lastrow = Sheets("JIRA_List").Cells(Rows.Count, 1).End(xlUp).Row
    
    
    Sheets("JIRA_List").Cells(i, 2).Copy
    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(erow, 1)
    
    Sheets("JIRA_List").Cells(i, 3).Copy
    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(erow, 2)
    
    Sheets("JIRA_List").Cells(i, 4).Copy
    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(erow, 3)
    
    Sheets("JIRA_List").Cells(i, 11).Copy
    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(erow, 4)
    
    End If
    
    Next i
    Application.CutCopyMode = False
    Sheets("Ageing JIRAs").Columns().AutoFit
    Range(“A7”).Select
    
    End Sub



    • Edited by keirax3 Friday, June 30, 2017 3:20 AM
    Wednesday, June 28, 2017 9:06 AM

Answers

  • Hi keirax3,

    I try to create one demo for you.

    in which I created 2 sheets with the same name mentioned by you and I place a button to copy the data from one sheet to another.

    when you click the button first it will clear the cell value and then after it will copy and paste the value.

    code:

    Private Sub CommandButton1_Click()
    Range("B11:D11").Clear
    Range("L11").Clear
    
    Sheets("jira list").Range("A7:C7").Copy
    Sheets("ageing jira").Activate
    Range("B11:D11").Select
    ActiveSheet.Paste
    
    Sheets("jira list").Range("D7").Copy
    Sheets("ageing jira").Activate
    Range("L11").Select
    ActiveSheet.Paste
    End Sub

    you can modify the code as per your requirement.

    Regards

    Deepak


    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, July 5, 2017 7:46 AM
    Moderator

All replies

  • Hi keirax3,

    You could use Match Function to find an existing data . Here is the example.

    lastrow = Sheets("JIRA_List").Cells(Rows.Count,2).End(xlUp).Row
    For i = 1 To lastrow
    issid = Sheets("JIRA_List").Cells(i, 2).Value
    erow = 0
    On Error Resume Next
    erow = Application.WorksheetFunction.Match(issid,Sheets("Ageing JIRAs").Range("A1:A17"), 0)
       If erow > 0 Then
            'update data
           Sheets("Ageing JIRAs").Cells(erow, 2) = Sheets("JIRA_List").Cells(i, 3)
           Sheets("Ageing JIRAs").Cells(erow, 3) = Sheets("JIRA_List").Cells(i, 4)
           Sheets("Ageing JIRAs").Cells(erow, 4) = Sheets("JIRA_List").Cells(i, 11)
        Else
            'add new data
        End If
    Next i

    What do you want to do by the clicking event? I suggest you create a new thread and provide more details of the requirement in the button click event.

    Best Regards,

    Terry

    Thursday, June 29, 2017 7:02 AM
  • It didn't work? I wanted to copy the corresponding field from jira list to ageing jira and apply formula.

    And the column in ageing jira is from A7, B7, C7, D7

    Jira list is from B11,C11,D11, L11 to ageing jira

    Friday, June 30, 2017 2:14 AM
  • Is it possible to delete the data in ageing jira first to able to copy the data from jira list? if so, how?
    Friday, June 30, 2017 10:18 AM
  • Hi keirax3,

    ->It didn't work?

    Did you get any error? Is there no data added or no data updated? You could use your original design to use copy and paste cell for applying formula. You said overwrite the existing data so I used match function to find an existing data for updating. If you want to add a new data, you could do it yourself in the If..Else statement.

    ->Is it possible to delete the data in ageing jira first to able to copy the data from jira list?

    Which data did you want to delete? All data in ageing jira? You could use Cells(Rows.Count,2).End(xlUp).Row to get last row index and use the index to delete all data in ageing jIra.

    Best Regards,

    Terry

    Tuesday, July 4, 2017 3:16 AM
  • I want to delete all the data in the cells from A7,B7,C7,D7 onwards than paste the data from another sheet. Could u give me the codes? I want a button to delete and paste the data, it does not matter if its duplicate data or not.
    Tuesday, July 4, 2017 9:59 AM
  • Hi keirax3,

    Here are the code. You could try to adjust it for your needed.

    Sub test()

    Application.ScreenUpdating = False

    Dim lastrowA As Long

    Dim lastrowJ As Long

    lastrowA = Sheets("Ageing JIRAs").Cells(Rows.Count, 1).End(xlUp).Row

    Sheets("Ageing JIRAs").Range("A7:D" & lastrowA).Delete

    lastrowJ = Sheets("JIRA_List").Cells(Rows.Count, 2).End(xlUp).Row

    For i = 11 To lastrowJ

    lastrowA = Sheets("Ageing JIRAs").Cells(Rows.Count, 1).End(xlUp).Row + 1

    If lastrowA < 7 Then

    lastrowA = 7

    End If

    Sheets("JIRA_List").Cells(i, 2).Copy

    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(lastrowA, 1)

    Sheets("JIRA_List").Cells(i, 3).Copy

    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(lastrowA, 2)

    Sheets("JIRA_List").Cells(i, 4).Copy

    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(lastrowA, 3)

    Sheets("JIRA_List").Cells(i, 11).Copy

    Sheet1.Paste Destination:=Sheets("Ageing JIRAs").Cells(lastrowA, 4)

    Next i

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

    End Sub

    Best Regards,

    Terry
    Wednesday, July 5, 2017 7:18 AM
  • Hi keirax3,

    I try to create one demo for you.

    in which I created 2 sheets with the same name mentioned by you and I place a button to copy the data from one sheet to another.

    when you click the button first it will clear the cell value and then after it will copy and paste the value.

    code:

    Private Sub CommandButton1_Click()
    Range("B11:D11").Clear
    Range("L11").Clear
    
    Sheets("jira list").Range("A7:C7").Copy
    Sheets("ageing jira").Activate
    Range("B11:D11").Select
    ActiveSheet.Paste
    
    Sheets("jira list").Range("D7").Copy
    Sheets("ageing jira").Activate
    Range("L11").Select
    ActiveSheet.Paste
    End Sub

    you can modify the code as per your requirement.

    Regards

    Deepak


    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, July 5, 2017 7:46 AM
    Moderator