Copying & pasting data in next available row in same worksheet.Solved/Closed

Question

Hello, 

I would really like assistance to write a vba code for a macro (using a drop down list) that copies data from A4:E4 and pastes it down in the next available row on the same worksheet.

Ideally would like a Yes/No drop down box, where if Yes is selected, the data is copied down to the next available row and if No is selected, no data is copied.

Thanks in advance

Matt

Configuration: Windows 7 / Mozilla 11.0

vcoolio · Answer

Hello Matt,

Is it always the range A4:E4 that is copied and moved down to the next available row beneath the data set?

Cheerio,
vcoolio.

vcoolio · Answer

Hello Matt,

Does this mean that each row will then have a drop down list in, say, Column F?

Cheerio,
vcoolio.

vcoolio · Answer

Hello Matt,

The following code should do the task for you:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub

If Target.Value = "Yes" Then
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "E")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
        End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code finds any cell in Column F that has a "Yes" value from the drop downs and moves the row of data (A:E) to the next available row at the bottom of the existing data set.

The code is a Worksheet_Change event and needs to be placed in the worksheet module. So, right click on the sheet tab, select "view code" from the menu that appears and then, in the big white field that appears, paste the above code. Go back to the worksheet and test it (remember to test the code in a copy of your work book first). Now, every time that you select "Yes", the relevant row of data will be moved to the bottom of the existing data set.

In the code above, I have just referenced sheet 1 as your work sheet. You may have to change this to suit yourself.

Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/go9pusbz9c206f2/Matt%28paste%20to%20next%20available%20row%20on%20same%20sheet%2C%20worksheet_change%20event%29.xlsm?dl=0

Select "Yes" from the drop downs in any cell in the Criteria column to see the code at work.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Matt,

You're welcome. Glad that I was able to help.

If you need the "used" row of data deleted after being moved, just let me know and we'll take it from there.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Matt,

Yes, its possible. Just so we get it right, upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Use dummy data and place a few notes showing exactly what you would like to do. It will make it easier for us to see how you are using your work book.

Thanks Matt. Have to go, my missus's birthday today. Trying to avoid the dog-house again!

Cheerio,
vcoolio.

vcoolio · Answer

Hello Matt,

Try the following:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

If Not Intersect(Target, Range("J:J")) Is Nothing Then
    If Target.Value = "No" Then
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet1.Range("A" & Rows.Count).End(3)(2)
        Range("G" & Rows.Count).End(xlUp).Value = Range("G" & Rows.Count).End(xlUp).Offset(, 1).Value + 1
    End If
End If

If Not Intersect(Target, Range("K:K")) Is Nothing Then
    If Target.Value = "Remove" Then
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "I")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
        Sheet3.Columns.AutoFit
    End If
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

You now have two criteria columns (J & K). Column K has the "Remove or Keep" drop downs so if you select "Remove", the relevant row of data will be transferred to Sheet3 and will be deleted from the Tracker sheet.
The code works as previous for Column J.

Following is the link to the updated test work book:-

https://www.dropbox.com/s/1jg78qr58cbrtbt/Matt%283%29.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

Copying & pasting data in next available row in same worksheet.

6 replies

Subscribe To Our Newsletter!